Writing formulae

  • Thread starter Thread starter SBond
  • Start date Start date
S

SBond

Help!
HI,
I wonder if anyone can help me at all. I want a formulae to calculate
the following but as I'm new to excel I'm not familiar with what
operators to use, what I can use etc. Please could someone help me
out?
if G12<=2000 then G12+1
elseif G12 >2000 AND G12<=3000 then G12+5
elseif G12>3000 AND G12<=30000 then G12+10
elseif G12>30000 AND G12<=50000 then G12+50
elseif G12>50000 AND G12<=100000 then G12+100
elseif G12>100000 AND G12<=1000000 then G12+1000
elseif G12>1000000 AND G12<=20000000 then G12+10000
elseif G12>20000000 AND G12<=30000000 then G12+50000
elseif G12>30000000 then G12+100000

(G12 is the specific cell that I wish to apply this to e.g. a price
field)

Thanks!
Sarah
 
one way:

Put your data in a table, say Sheet2!A1:B9

A B
1 Value Adder
2 0 1
3 2000.01 5
4 3000.01 10
5 30000.01 50
6 50000.01 100
7 100000.01 1000
8 1000000.01 10000
9 20000000.01 50000
10 30000000.01 100000

Then, back in Sheet1:

=G12 + VLOOKUP(G12, Sheet2!A:B, 2, TRUE)
 
Hey, not to worry.
Got there in the end:
=IF(G12<2000,G12+1,IF(G12<3000,G12+5,IF(G12<30000,G12+10,IF(G12<50000,G12+50,IF(G12<100000,G12+100,IF(G12<1000000,G12+1000,IF(G12<20000000,G12+10000,IF(G12<30000000,G12+50000,G12+100000))))))))
 
Maybe just another option similar to J.E. excellent idea...

=G12+LOOKUP(G12,
{0,2000,3000,30000,50000,100000,1000000,20000000,30000000},
{1,5,10,50,100,1000,10000,50000,100000})

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


SBond said:
Hey, not to worry.
Got there in the end:
Click to expand...
=IF(G12<2000,G12+1,IF(G12<3000,G12+5,IF(G12<30000,G12+10,IF(G12<50000,G12+50
,IF(G12<100000,G12+100,IF(G12<1000000,G12+1000,IF(G12<20000000,G12+10000,IF(
G12<30000000,G12+50000,G12+100000))))))))
 
A B
1 9.00E+307 100000
2 30000000 50000
3 20000000 10000
4 1000000 1000
5 100000 100
6 50000 50
7 30000 10
8 3000 5
9 2000 1

=G12+INDEX(B1:B9,MATCH(G12,A1:A9,-1),1)
 
Back
Top