Calculate In-Between Numbers

[Brenda Rueter]

User wants to combine within one formula:
If C8 < x,then C6-90
If C8 > x but under xx, C6-120
If C8 > xx but < xxx, C6-150
If C8 > xxx, C6-180

Is there a function that will do the in-between parts for me?

 

[Guest]

=IF(C8<A1,C6-90,IF(C8<A2,C6-120,IF(C8<A3,C6-150,C6-180)))

a1 = x
a2 = xx
a3 = xxx

 

[Brenda Rueter]

This does not take into account >x but < than xx.
Let's say
a1=100
a2=200
a3=300

we want smaller than 300 but larger than 200. That's the part we're having
trouble putting together. The straight nesting IF statement is no problem.

 

[Leo Heuser]

Brenda

One way:

=C6-(C8<x)*90-(AND(C8>=x,C8<xx))*120-(AND(C8>=xx,C8<xxx))*150-(C8>=xxx)*180

assuming >=x, >=xx and >=xxx

 

[arno]

Hi Brenda,

User wants to combine within one formula:
If C8 < x,then C6-90
If C8 > x but under xx, C6-120
If C8 > xx but < xxx, C6-150
If C8 > xxx, C6-180

Is there a function that will do the in-between parts for me?

lookup() will do the job for you, have a look in excel help. however,
make sure you get correct results if eg. c8=xx (exactly xx, not
smaller, not bigger).

you need a table "data" like this one
0 90
x 120
xx 150
xxx 180


then you can use
=c6-lookup(c8,data,2,TRUE)


arno

 

[Markus L]

User wants to combine within one formula:
If C8 < x,then C6-90
If C8 > x but under xx, C6-120
If C8 > xx but < xxx, C6-150
If C8 > xxx, C6-180

Brenda, try this one:
=IF(C8>xxx,C6-180,IF(C8>xx,C6-150,IF(C8>x,C6-120,C6-90)))
Not elegant at all, a simple translation of your requirements.

 

[arno]

ooops:

it should be VLOOKUP!

arno

 

[Brenda Rueter]

Thanks everyone. I'm working with the different solutions offered here.