| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I...
1 am making a cheat sheet for sales tax in a1 i have the sales tax. a2 -
a1001 i have $.01 to $100.00. b2-b1001 i have the formula that says what the tax is. so for $.01 to $.07 is $.00. $.08 to $.23 is $.01. My question is how do set it up so on a diffrent page it would pull the info so the lay out would be (A1) $.01 (B1)- (C1)$.07 (D1)$.00 (A2)$.08 (B2)- (C2)$.23 (D2)$.01 and so on. Can anyone help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I...
Not the easiest task in the world. Here I'll assume your list is on
Sheet1. Then, on Sheet2, do the following: 1. Select a decent range of cells, preferably matching to the number of tax rates you have, in column D. 2. Paste this formula in the formula bar: =INDEX(Sheet1!$B$1:$B$1001,SMALL(IF(MATCH(Sheet1!$ B$1:$B$1001,Sheet1!$B $1:$B$1001,0)=ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$ B $1001))),MATCH(Sheet1!$B$1:$B$1001,Sheet1!$B$1:$B $1001,0),""),ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$B $1001))))) 3. Press Ctrl+Shift+Enter to commit. This is an array-formula, so simply pressing Enter will not work. 4. In cell A1, type in 0.01 5. In cell A2, type in =C1+0.01 and copy down 6. In cell C1, type this formula and press Enter: =IF(ISERROR(INDEX(Sheet1!$A$1:$A$1001,MATCH(Sheet2 !D2,Sheet1!$B$1:$B $1001,0)-1)),MAX(Sheet1!$A$1:$A$1001),INDEX(Sheet1!$A$1:$A $1001,MATCH(Sheet2!D2,Sheet1!$B$1:$B$1001,0)-1)) 7. In cell B1, type in - and copy down. Hope that helps. On Nov 1, 12:33 pm, lee willis <lee wrote: 1 am making a cheat sheet for sales tax in a1 i have the sales tax. a2 - a1001 i have $.01 to $100.00. b2-b1001 i have the formula that says what the tax is. so for $.01 to $.07 is $.00. $.08 to $.23 is $.01. My question is how do set it up so on a diffrent page it would pull the info so the lay out would be (A1) $.01 (B1)- (C1)$.07 (D1)$.00 (A2)$.08 (B2)- (C2)$.23 (D2)$.01 and so on. Can anyone help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I...
Sorry, in column C, also copy down the formula from C1.
On Nov 1, 2:24 pm, ilia wrote: Not the easiest task in the world. Here I'll assume your list is on Sheet1. Then, on Sheet2, do the following: 1. Select a decent range of cells, preferably matching to the number of tax rates you have, in column D. 2. Paste this formula in the formula bar: =INDEX(Sheet1!$B$1:$B$1001,SMALL(IF(MATCH(Sheet1!$ B$1:$B$1001,Sheet1!$B $1:$B$1001,0)=ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$ B $1001))),MATCH(Sheet1!$B$1:$B$1001,Sheet1!$B$1:$B $1001,0),""),ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$B $1001))))) 3. Press Ctrl+Shift+Enter to commit. This is an array-formula, so simply pressing Enter will not work. 4. In cell A1, type in 0.01 5. In cell A2, type in =C1+0.01 and copy down 6. In cell C1, type this formula and press Enter: =IF(ISERROR(INDEX(Sheet1!$A$1:$A$1001,MATCH(Sheet2 !D2,Sheet1!$B$1:$B $1001,0)-1)),MAX(Sheet1!$A$1:$A$1001),INDEX(Sheet1!$A$1:$A $1001,MATCH(Sheet2!D2,Sheet1!$B$1:$B$1001,0)-1)) 7. In cell B1, type in - and copy down. Hope that helps. On Nov 1, 12:33 pm, lee willis <lee wrote: 1 am making a cheat sheet for sales tax in a1 i have the sales tax. a2 - a1001 i have $.01 to $100.00. b2-b1001 i have the formula that says what the tax is. so for $.01 to $.07 is $.00. $.08 to $.23 is $.01. My question is how do set it up so on a diffrent page it would pull the info so the lay out would be (A1) $.01 (B1)- (C1)$.07 (D1)$.00 (A2)$.08 (B2)- (C2)$.23 (D2)$.01 and so on. Can anyone help?- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I...
close the issue is on sheet 1 E1 is 6.25% and B1 =A1*E1 which equals
..000625. so there is no match. "ilia" wrote: Not the easiest task in the world. Here I'll assume your list is on Sheet1. Then, on Sheet2, do the following: 1. Select a decent range of cells, preferably matching to the number of tax rates you have, in column D. 2. Paste this formula in the formula bar: =INDEX(Sheet1!$B$1:$B$1001,SMALL(IF(MATCH(Sheet1!$ B$1:$B$1001,Sheet1!$B $1:$B$1001,0)=ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$ B $1001))),MATCH(Sheet1!$B$1:$B$1001,Sheet1!$B$1:$B $1001,0),""),ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$B $1001))))) 3. Press Ctrl+Shift+Enter to commit. This is an array-formula, so simply pressing Enter will not work. 4. In cell A1, type in 0.01 5. In cell A2, type in =C1+0.01 and copy down 6. In cell C1, type this formula and press Enter: =IF(ISERROR(INDEX(Sheet1!$A$1:$A$1001,MATCH(Sheet2 !D2,Sheet1!$B$1:$B $1001,0)-1)),MAX(Sheet1!$A$1:$A$1001),INDEX(Sheet1!$A$1:$A $1001,MATCH(Sheet2!D2,Sheet1!$B$1:$B$1001,0)-1)) 7. In cell B1, type in - and copy down. Hope that helps. On Nov 1, 12:33 pm, lee willis <lee wrote: 1 am making a cheat sheet for sales tax in a1 i have the sales tax. a2 - a1001 i have $.01 to $100.00. b2-b1001 i have the formula that says what the tax is. so for $.01 to $.07 is $.00. $.08 to $.23 is $.01. My question is how do set it up so on a diffrent page it would pull the info so the lay out would be (A1) $.01 (B1)- (C1)$.07 (D1)$.00 (A2)$.08 (B2)- (C2)$.23 (D2)$.01 and so on. Can anyone help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I...
I don't understand. Are you saying the rates in column B of Sheet1
are coming from a formula? That shouldn't affect the above solution, because the lookup is based on the tax rate, not the range within which it's applicable. On Nov 1, 4:39 pm, lee willis wrote: close the issue is on sheet 1 E1 is 6.25% and B1 =A1*E1 which equals .000625. so there is no match. "ilia" wrote: Not the easiest task in the world. Here I'll assume your list is on Sheet1. Then, on Sheet2, do the following: 1. Select a decent range of cells, preferably matching to the number of tax rates you have, in column D. 2. Paste this formula in the formula bar: =INDEX(Sheet1!$B$1:$B$1001,SMALL(IF(MATCH(Sheet1!$ B$1:$B$1001,Sheet1!$B $1:$B$1001,0)=ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$ B $1001))),MATCH(Sheet1!$B$1:$B$1001,Sheet1!$B$1:$B $1001,0),""),ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$B $1001))))) 3. Press Ctrl+Shift+Enter to commit. This is an array-formula, so simply pressing Enter will not work. 4. In cell A1, type in 0.01 5. In cell A2, type in =C1+0.01 and copy down 6. In cell C1, type this formula and press Enter: =IF(ISERROR(INDEX(Sheet1!$A$1:$A$1001,MATCH(Sheet2 !D2,Sheet1!$B$1:$B $1001,0)-1)),MAX(Sheet1!$A$1:$A$1001),INDEX(Sheet1!$A$1:$A $1001,MATCH(Sheet2!D2,Sheet1!$B$1:$B$1001,0)-1)) 7. In cell B1, type in - and copy down. Hope that helps. On Nov 1, 12:33 pm, lee willis <lee wrote: 1 am making a cheat sheet for sales tax in a1 i have the sales tax. a2 - a1001 i have $.01 to $100.00. b2-b1001 i have the formula that says what the tax is. so for $.01 to $.07 is $.00. $.08 to $.23 is $.01. My question is how do set it up so on a diffrent page it would pull the info so the lay out would be (A1) $.01 (B1)- (C1)$.07 (D1)$.00 (A2)$.08 (B2)- (C2)$.23 (D2)$.01 and so on. Can anyone help?- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I...
yes it is a formula in column B its 6.25% * $.01 to $100.01 so it is a
diffrent value for .01 than .02. thats why i am having issues. "ilia" wrote: I don't understand. Are you saying the rates in column B of Sheet1 are coming from a formula? That shouldn't affect the above solution, because the lookup is based on the tax rate, not the range within which it's applicable. On Nov 1, 4:39 pm, lee willis wrote: close the issue is on sheet 1 E1 is 6.25% and B1 =A1*E1 which equals .000625. so there is no match. "ilia" wrote: Not the easiest task in the world. Here I'll assume your list is on Sheet1. Then, on Sheet2, do the following: 1. Select a decent range of cells, preferably matching to the number of tax rates you have, in column D. 2. Paste this formula in the formula bar: =INDEX(Sheet1!$B$1:$B$1001,SMALL(IF(MATCH(Sheet1!$ B$1:$B$1001,Sheet1!$B $1:$B$1001,0)=ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$ B $1001))),MATCH(Sheet1!$B$1:$B$1001,Sheet1!$B$1:$B $1001,0),""),ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$B $1001))))) 3. Press Ctrl+Shift+Enter to commit. This is an array-formula, so simply pressing Enter will not work. 4. In cell A1, type in 0.01 5. In cell A2, type in =C1+0.01 and copy down 6. In cell C1, type this formula and press Enter: =IF(ISERROR(INDEX(Sheet1!$A$1:$A$1001,MATCH(Sheet2 !D2,Sheet1!$B$1:$B $1001,0)-1)),MAX(Sheet1!$A$1:$A$1001),INDEX(Sheet1!$A$1:$A $1001,MATCH(Sheet2!D2,Sheet1!$B$1:$B$1001,0)-1)) 7. In cell B1, type in - and copy down. Hope that helps. On Nov 1, 12:33 pm, lee willis <lee wrote: 1 am making a cheat sheet for sales tax in a1 i have the sales tax. a2 - a1001 i have $.01 to $100.00. b2-b1001 i have the formula that says what the tax is. so for $.01 to $.07 is $.00. $.08 to $.23 is $.01. My question is how do set it up so on a diffrent page it would pull the info so the lay out would be (A1) $.01 (B1)- (C1)$.07 (D1)$.00 (A2)$.08 (B2)- (C2)$.23 (D2)$.01 and so on. Can anyone help?- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
