IF Nested Function

[murkaboris]

Hello:

I'm trying to create a nested function that needs to use multiple conditions
to return a specific value.
Here is an example of the data.

X Point Value Discount Point Value
27.00% <= 20% 18
29.00% 20% <= x < 22% 16
34.00% 23 <= x < 25% 14
37.00% 26% <= x < 28% 12
21.00% 29% <= x < 32% 10
39.00% 33% <= x < 35% 8
36% <= x <= 38% 4
x > 38% 0

I need to return the Point value for the "X" column based on the "Discount"
condition. So if my "X" value in the first column is 27% I should get 12 as a
returned Point Value.
Any advise would be greatly appreciated.
Thank you.

 

[francis]

try this in B2 and copy down

=IF(A2<H2,I2,IF(A2<H3,I3,IF(A2<H4,I4,IF(A2<H5,I5,IF(A2<H6,I6,IF(A2<H7,I7,IF(A2<H8,I8,I9)))))))

You may want to consider to recreate a table with the Discount from and to in
order to use Lookup function which may be better than the nested ifs in the
event
you may run into the limitation of the seven ifs that Excel has.

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[francis]

Oops! I forgot to mention that you need to create a column to hold
the cap of your discount values, ie 20%,22%,25%,28%....
I did this in col H and the point values are in col I.
Adjust the range to suit yours.


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[Alojz]

Hi, as u have gaps between 22%-23%, 25%-26% etc., I rearranged it to make it
working for fractions of percents, too. I split the column discount from - to
to two columns. Here is the adjusted table - filled in A1:E9 (row 1 is the
header row):
A B C D E
x point value discount > discount <= point value
27% 20% 18
29% 20% 22% 16
34% 22% 25% 14
37% 25% 28% 12
21% 28% 32% 10
39% 32% 35% 8
35% 38% 4
38% 0

insert formula in B2: =SUM((A2>$C$2:$C$9)*(A2<=$D$2:$D$9)*$E$2:$E$9)
press ctrl+shift+enter (array formula). Drag and copy down to B3:B7

HTH, please, click yes, if so.

 

[murkaboris]

Thank you Ashish for your efforts but it didn't work, the only answer I got
was "0" for any X value.
murkaboris

 

[murkaboris]

Hi Francis:

thank you for your help. It worked with a few alterations. I had to include
<= operation to make sure it returns the correct values and make the discount
column and point value rate fixed cells i.e. "$H$2, $I,$2" etc... then it
worked like a magic.
Thanks again!

murkaboris

 

[murkaboris]

Hello Alojz:

Absolute magic! Thank you for your help!

Murkaboris

 

[murkaboris]

Hello Alojz:

One more question if you don't mind please.
I'm getting the correct values if the percentages are between 20% through
38%. If I have for example 9%, which is less than 20% and I would expect the
formula to return 18 as a result its returning 0. It seems like anything
below or above the range of the new table it doesn't work.
Any ideas?
Thank you.

Murkaboris

 

[Alojz]

I was not able to simulate it, when I insert 9% in A2, A10 or whatever A, in
B1, B10 or corresponding B I receive 18, which is correct. Did u fix the area
in formula as I wrote, meaning $C$2:$C$9, $D$2:$D$9, $E$2:$E$9? It cannot
work with C2:C9, D29, E2:E9 when copying down. Check if dollar signs are
everywhere where they should be.

 

[murkaboris]

Hello Alojz:

Yes I have the area typed correctly with the "$". It works on all entries
but anything below 20% (including 20%), I always get 0 as a result for
anything below 20%. I've even tried to recreate the whole worksheet and still
am getting the same result. For my xls the column "A" in your formula is
column "H", need to return value in column "K", column "C" from formula is
column "W", column "D" from formula is column "X" and column "E" from formula
is column "Y".
The formula I'm using is: =SUM((H2>$W$2:$W$8)*(H2<=$X$2:$X$8)*$Y$2:$Y$8).
Thank you

Murkaboris

 

[Alojz]

Boris,

data in my example are:
row 1 - header row
row2-row9 - rows with data, with the matrix from-to and point value in area
C2:E9, C2 & D9 are blank, u can put in C2 0% and in D9 100% if u wish.
Look at it once again. Then the formula must refer to rows 2-9 not rows 2-8!!!
I guess this makes the whole mess in your case. Try to check one more time,
if no success, gimme ur e-mail, will send the file to u, with my A-E columns
together with your H,K,W,X,Y on two separate sheets.

Regards,
Alojz

 

[murkaboris]

Hi Alojz:

I had to recreate the whole xls into a new file and then it worked.
Not sure what was going on.
I'm all set now, thanks again for all your help.
Array formula is the magic .
Thanks

Murkaboris

 

[Alojz]

Boris, I uploaded sample file with both layout of data to:

http://ash001.sweb.cz/IF_NESTED_FUNCTION.xls


One more idea where may be ur problem: Is ur formula array-entered, so do u
see curly brackets around it?

 

[Alojz]

All rite, then. In between, I uploaded the file to web. Thx for feedback,
glad it works for u.

 

[Alojz]

After all, I guess my last remark was correct; I suspect u did not
array-enter for new cell. If u hit just enter, result would be 0, be
carefull. Sorry I did not realize earlier. Whatever, it works for u now