Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to make a mark up table with more than 7 if statements
=IF(A3<4.99,A3*6,IF(A3<9.9,(A3*6.3)+5,IF(A3<24.99, (A3*6)+15,IF(A3<49.99,(A3*5.8)+12,IF(A3<74.99,(A3* 5.5)+8,IF(A3<99.99,(A3*5.25)+8,IF(A3<149.99,(A3*5. 25)+8)))))))
this is the formula so far but i need more than 7 statements is there a way to put more than 7? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to make a mark up table with more than 7 if statements
Not directly. There are only 7 levels in a nested IF.
If it really has to work like this, your last statement in cell1 could just be ....IF(A3<149.99,(A3*5.25)+8,"not found"))))))) Then you can have another cell2 on the right hand side, saying =IF(cell1<"not found","",IF(A3<200, whatever,IF(A3<250, whatever,) etc etc etc You can then recombine the 2 results in a third cell. =IF(cell1="not found",cell2,cell1) and you can hide the intermediate columns. -- Allllen "Digace" wrote: =IF(A3<4.99,A3*6,IF(A3<9.9,(A3*6.3)+5,IF(A3<24.99, (A3*6)+15,IF(A3<49.99,(A3*5.8)+12,IF(A3<74.99,(A3* 5.5)+8,IF(A3<99.99,(A3*5.25)+8,IF(A3<149.99,(A3*5. 25)+8))))))) this is the formula so far but i need more than 7 statements is there a way to put more than 7? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to make a mark up table with more than 7 if statements
Try:
Set up a table as shown below with your "multiplier" and "addition" value. Table must be in descending order as shown. I J K 49.99 5.8 12 '<=== row 2 24.99 6.0 15 9.99 6.3 5 4.99 6.0 0 Use the following formula: =A3*INDEX(I2:K5,MATCH(A3,I2:I5,-1),2)+INDEX(I2:K5,MATCH(A3,I2:I5,-1),3) HTH "Allllen" wrote: Not directly. There are only 7 levels in a nested IF. If it really has to work like this, your last statement in cell1 could just be ...IF(A3<149.99,(A3*5.25)+8,"not found"))))))) Then you can have another cell2 on the right hand side, saying =IF(cell1<"not found","",IF(A3<200, whatever,IF(A3<250, whatever,) etc etc etc You can then recombine the 2 results in a third cell. =IF(cell1="not found",cell2,cell1) and you can hide the intermediate columns. -- Allllen "Digace" wrote: =IF(A3<4.99,A3*6,IF(A3<9.9,(A3*6.3)+5,IF(A3<24.99, (A3*6)+15,IF(A3<49.99,(A3*5.8)+12,IF(A3<74.99,(A3* 5.5)+8,IF(A3<99.99,(A3*5.25)+8,IF(A3<149.99,(A3*5. 25)+8))))))) this is the formula so far but i need more than 7 statements is there a way to put more than 7? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to make a mark up table with more than 7 if statements
"Digace" wrote in message
... =IF(A3<4.99,A3*6,IF(A3<9.9,(A3*6.3)+5,IF(A3<24.99, (A3*6)+15,IF(A3<49.99,(A3*5.8)+12,IF(A3<74.99,(A3* 5.5)+8,IF(A3<99.99,(A3*5.25)+8,IF(A3<149.99,(A3*5. 25)+8))))))) this is the formula so far but i need more than 7 statements is there a way to put more than 7? LOOKUP sounds the best bet. Try the help for VLOOKUP. -- David Biddulph |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to make a mark up table with more than 7 if statements
Hi,
The best way to handle this sort of arrangement is usually via a lookup table and LOOKUP formula. The table could be constructed like: Col A Col B 004.99 =A3*6 009.90 =A3*6.3+5 024.99 =A3*5.8+12 074.99 =A3*5.5+8 099.99 =A3*5.25+8 149.99 =A3*5.25+8 999999 =A3*5+8 I'm assuming there's an error in your post, since the last two tests have the same true result; otherwise the second-last one could be deleted. You also have no test for values =149.99, so note the made-up test at the end for values <999999. See Excel's help file for how to code the LOOKUP. If there are objections to a lookup solution, you can achieve the result with an in-line formula and NO if statements, as in: =(A3<4.99)*A3*6+(A3=4.99)*(A3<9.9)*((A3*6.3)+5)+( A3=9.9)*(A3<24.99)*((A3*5 ..8)+12)+(A3=24.99)*(A3<74.99)*((A3*5.5)+8)+(A3= 74.99)*(A3<99.99)*((A3*5.25 )+8)+(A3=99.99)*(A3<149.99)*((A3*5.25)+8)+(A3=14 9.99)*((A3*5)+8) Again, note the made-up test at the end for values =149.99. Cheers -- macropod [MVP - Microsoft Word] "Digace" wrote in message ... =IF(A3<4.99,A3*6,IF(A3<9.9,(A3*6.3)+5,IF(A3<24.99, (A3*6)+15,IF(A3<49.99,(A3* 5.8)+12,IF(A3<74.99,(A3*5.5)+8,IF(A3<99.99,(A3*5.2 5)+8,IF(A3<149.99,(A3*5.25 )+8))))))) this is the formula so far but i need more than 7 statements is there a way to put more than 7? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling from a table to make a timeline | Excel Worksheet Functions | |||
Make Excel Table fit into Word Doucument | New Users to Excel | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
i need to make a pub quiz league fixture table | Excel Discussion (Misc queries) | |||
how do I make hard carriage return appear in pivot table? | Excel Discussion (Misc queries) |