Calculate In-Between Numbers
 

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?

=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" wrote:

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?

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.

"LanceB" wrote in message
...
=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" wrote:

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?

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Brenda Rueter" skrev i en meddelelse
...
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?

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

"Brenda Rueter" wrote in message
...
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(C8xxx,C6-180,IF(C8xx,C6-150,IF(C8x,C6-120,C6-90)))
Not elegant at all, a simple translation of your requirements.

ooops:

it should be VLOOKUP!

arno

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

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

"Markus L" wrote in message
...
"Brenda Rueter" wrote in message
...
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(C8xxx,C6-180,IF(C8xx,C6-150,IF(C8x,C6-120,C6-90)))
Not elegant at all, a simple translation of your requirements.