multiple if-then results

[chesscoop]

I can't seem to figure out a formula to get different outputs fo
different ranges of inputs. I need something that would yield:

if B:19<100, then D19=0, if B:19> or = to 100 and <300, then D19=.03
if B:19> or = to 300 and <600, then D19=.06, etc... and the range
will go up to 7500 incrementally with outputs up to .2

 

[Norman Harker]

Hi Chesscoop!

Use a VLOOKUP table.

In K1:L27 I have:
0 0
100 0.3
300 0.6
600 0.85
900 1.1
1200 1.35
1500 1.6
1800 1.85
2100 2.1
2400 2.35
2700 2.6
3000 2.85
3300 3.1
3600 3.35
3900 3.6
4200 3.85
4500 4.1
4800 4.35
5100 4.6
5400 4.85
5700 5.1
6000 5.35
6300 5.6
6600 5.85
6900 6.1
7200 6.35
7500 6.6


My formula in D19 is:
=VLOOKUP(B19,$K$1:$L$27,2)

But I might have interpreted what you want above 600.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
I would use a lookup table with your conditions and the associated
value. Only use the lower boundary for this. So something like
A B
1 0 0
2 100 0.3
3 300 0.6
....

is this is in a separate sheet ('lookup') try
=VLOOKUP(B19,'lookup'!$A$1:$B$100,2)