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?

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?

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?

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

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?