desperately need help with IF function please

Hello all,

I need to create a table calculation as below scenario

If A=100%, then value 123, but if A<=114% and A>=100%, then value 234, but if A<=115% and A>=120%, then value 456, but if A>=120% then value 456

pls help

sorry, let me correct the condition

if A >= 100, then value 123 == this I can do for the first function / cell

For the second cell
but, if A>=100 and A<115% then value 234, if not, value 0
but, if A>=115% and A <120% then value 345, if not, value 0
but, if A>=120% then value 456, if not, value 0

can we do these in 1 function ? (for second cell)

thx in adv

anyone can help me pls

Originally Posted by hendrilearning

sorry, let me correct the condition

if A >= 100, then value 123 == this I can do for the first function / cell

For the second cell
but, if A>=100 and A<115% then value 234, if not, value 0
but, if A>=115% and A <120% then value 345, if not, value 0
but, if A>=120% then value 456, if not, value 0

can we do these in 1 function ? (for second cell)

thx in adv

Try
=LOOKUP(A1,{100%,115%,120%},{234,345,456})

Note, LESS than 100% will result in #N/A

Originally Posted by Jonmo1

Try
=LOOKUP(A1,{100%,115%,120%},{234,345,456})

Note, LESS than 100% will result in #N/A

It didn't work ou

Here's the sheet I need to create (on incentives earn column)


SCHEME POTENTIAL INCENTIVES ACHIEVEMENT INCENTIVES earn
(=)100% 100 128% 100
101-114% 200 0
115-120% 300 0
(>=)120% 400 400

That doesn't look like a sheet...

Can you attach an actual book?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Originally Posted by Jonmo1

That doesn't look like a sheet...

Can you attach an actual book?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Please see my attachment

So with that example, you want a total of 500?

100 for simply achieving 100% or more.
PLUS
400 for going over 120%

If the number was say 117%, then it would be 400 (100 + 300 for 115%-120%)

Is that right?

Originally Posted by Jonmo1

So with that example, you want a total of 500?

100 for simply achieving 100% or more.
PLUS
400 for going over 120%

If the number was say 117%, then it would be 400 (100 + 300 for 115%-120%)

Is that right?

correct, what I want is to create function for the incentive earn figure, If 100% then earn 100, if 117% then additional earn 300, but if 120% then additonal earn will be 400
for the total I can just sum all the incentives. function for each row in that column is the important one

Originally Posted by bebo021999

Can not stuff in one formula, I think.

E7 = IF($D$7=1,C7,0)

E8 = IF(AND($D$7>=1.01,$D$7<=1.14),200,0)

E9 = IF(AND($D$7>=1.15,$D$7<1.2),300,0)

E10 = IF($D$7>=1.2,400,0)

Great !!

Learnt someting here

thx a lot

why can't I just use D7 instead of $D$7 ?

Try
=IF(D7>=100%,100,0)+LOOKUP(D7,{0,1.01,1.15,1.2},{0,200,300,400})

Originally Posted by Jonmo1

Try
=IF(D7>=100%,100,0)+LOOKUP(D7,{0,1.01,1.15,1.2},{0,200,300,400})

Tried that, it won't work for selecting the cell number (for the value), I prefer to select the cell number rather than type the exact value

Originally Posted by bebo021999

You are calculating incentives for D7 with multi criterias, using absolute address to avoid changing reference.

Anyway, if you interested in the total only, use this:

=LOOKUP($D$7,{1,1.01,1.15,1.2},{100,200,300,400})

No, I'm more concern in calculating each row, for the total I can just sum down at the end of row

Thanks all for your help, I've solved my need

Learnt something today

thx a lot

Originally Posted by hendrilearning

Tried that, it won't work for selecting the cell number (for the value), I prefer to select the cell number rather than type the exact value

In that case, make your values in column B be just the low end of each percentage range.
As you have it now, they're just text strings and can't really be used in a formula.

See example..

EFhendrilearning.xlsx

Originally Posted by Jonmo1

In that case, make your values in column B be just the low end of each percentage range.
As you have it now, they're just text strings and can't really be used in a formula.

See example..

Attachment 376976

wow, nice !

But it didn't really fit my purpose

=IF(D7>=100%,100 = I must entry the exact amount which is huge, if I try selecting the cell, copy to other row will have wrong calculation,0)+IFERROR(LOOKUP(D7,$B$8:$C$10),0)

Besides, I need detail value per row

But this is nice, I could use this on other

thx a lot