Formula Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JoeH - 04 Jul 2007 06:25 GMT

I am trying to develop a worksheet where my sum will vary depending on
whether another cell is greater than one number, greater than or less than
another number and less than another number. A second multiplier depends on
this criteria. An example would be if cell L20was <30 then C20 would be
multiplied by cell L20. If it was <36> It would be multiplied by I20and if
it was >36 it would be multiplied by cell F20. I am trying to enter the
formula into cell M20. Hope this makes sense. Thanks in advance.

Reply to this Message

joeu2004 - 04 Jul 2007 07:27 GMT

> An example would be if cell L20was <30 then C20 would be
> multiplied by cell L20. If it was <36> It would be multiplied by I20and if
> it was >36 it would be multiplied by cell F20. I am trying to enter the
> formula into cell M20. Hope this makes sense.

=C20 * if(L20<30, L20, if(L20>36, F20, I20))

Reply to this Message

JoeH - 04 Jul 2007 20:07 GMT

Thanks for the help. I was suffering from brain cramps, but it all makes
sense now that I got some sleep and looked over your reply.

>> An example would be if cell L20was <30 then C20 would be
>> multiplied by cell L20. If it was <36> It would be multiplied by I20and
[quoted text clipped - 3 lines]
>
> =C20 * if(L20<30, L20, if(L20>36, F20, I20))

Reply to this Message

T. Valko - 04 Jul 2007 07:43 GMT

You need to define the intervals more clearly.

When a lot of people post this type of question they don't realize that they
have either defined overlapping intervals or left out a number that's the
boundary of an interval.

Try defining them like this:

<= X (less than or equal to)

>= X but <= X (greater than or equal to but less than or equal to)
>= X (greater than or equal to)

For example:

<=30

>= 31 but <=35
>=36

Signature

Biff
Microsoft Excel MVP

>I am trying to develop a worksheet where my sum will vary depending on
>whether another cell is greater than one number, greater than or less than
[quoted text clipped - 3 lines]
>it was >36 it would be multiplied by cell F20. I am trying to enter the
>formula into cell M20. Hope this makes sense. Thanks in advance.

Reply to this Message