If Then Else Formula for Excel Worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pabell - 11 Sep 2007 21:12 GMT

I need a formula that evaluates a cell and if that cell is 15000 or below the
spreadsheet multiples another cell by a certain percent, putting the result
in the current cell, if the first cell is between 15001 and 25000 it
multiplies by another percent. This goes on for four total iterations. Can
anyone help me?

Signature

-pbell

Reply to this Message

JNW - 11 Sep 2007 21:26 GMT

A1 is the cell we are testing. B1 is the cell we are multiplying.
You don't need an IF statement for the last percentage assuming you want any
other number other than the ones you've defined previously to trigger the
next percentage.

=if(a1<15000,B1*15%,if(and(a1>=15001,A1<=25000),b1*20%,if(and(a1>=25001,a1<=35000),b1*25%,b1*30%)))

Signature

JNW

> I need a formula that evaluates a cell and if that cell is 15000 or below the
> spreadsheet multiples another cell by a certain percent, putting the result
> in the current cell, if the first cell is between 15001 and 25000 it
> multiplies by another percent. This goes on for four total iterations. Can
> anyone help me?

Reply to this Message

Sandy Mann - 11 Sep 2007 21:30 GMT

You don't give many details but try something like:

=G6*LOOKUP(F6,{0,15000,25000,35000,45000},{0.05,0.1,0.15,0.2,0.25})

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>I need a formula that evaluates a cell and if that cell is 15000 or below
>the
[quoted text clipped - 4 lines]
> Can
> anyone help me?

Reply to this Message

Teethless mama - 11 Sep 2007 23:26 GMT

You can make the formula slightly shorter by doing this:

=G6*LOOKUP(F6,{0,15,25,35,45}*1000,{0.05,0.1,0.15,0.2,0.25})

> You don't give many details but try something like:
>
[quoted text clipped - 8 lines]
> > Can
> > anyone help me?

Reply to this Message

Sandy Mann - 12 Sep 2007 00:10 GMT

Thank you Teethless mama, I didn't know that LOOKUP() would accept an array
like that.

Signature

Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> You can make the formula slightly shorter by doing this:
>
[quoted text clipped - 13 lines]
>> > Can
>> > anyone help me?

Reply to this Message

T. Valko - 12 Sep 2007 05:40 GMT

But you've made it less efficient by adding an unnecessary step in the
calculation process. Shorter isn't always better.

http://tinyurl.com/2xby3d

Signature

Biff
Microsoft Excel MVP

> You can make the formula slightly shorter by doing this:
>
[quoted text clipped - 13 lines]
>> > Can
>> > anyone help me?

Reply to this Message