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?
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" <[email protected]> wrote in message
news:[email protected]...
> =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" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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.
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" <[email protected]> wrote in message
news:[email protected]...
> "Brenda Rueter" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks