Need to make a mark up table with more than 7 if statements

[Guest]

=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?

 

[Guest]

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.

 

[Guest]

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

 

[David Biddulph]

=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.

 

[macropod]

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>=149.99)*((A3*5)+8)
Again, note the made-up test at the end for values >=149.99.

Cheers