Calculate In-Between Numbers

B

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

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

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
 
A

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?
Click to expand...

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
 
M

Markus L

Brenda Rueter said:
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
Click to expand...

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