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.

try this in B2 and copy down

=IF(A2<H2,I2,IF(A2<H3,I3,IF(A2<H4,I4,IF(A2<H5,I5,I F(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



"murkaboris" wrote:

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.

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






"francis" wrote:

try this in B2 and copy down

=IF(A2<H2,I2,IF(A2<H3,I3,IF(A2<H4,I4,IF(A2<H5,I5,I F(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



"murkaboris" wrote:

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.

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

"francis" wrote:

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






"francis" wrote:

try this in B2 and copy down

=IF(A2<H2,I2,IF(A2<H3,I3,IF(A2<H4,I4,IF(A2<H5,I5,I F(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



"murkaboris" wrote:

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.

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" wrote:

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.

Hello Alojz:

Absolute magic! Thank you for your help!

Murkaboris

"Alojz" wrote:

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" wrote:

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.

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" wrote:

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" wrote:

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.

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, D2:D9, E2:E9 when copying down. Check if dollar signs are
everywhere where they should be.

"murkaboris" wrote:

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" wrote:

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" wrote:

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.

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" wrote:

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, D2:D9, E2:E9 when copying down. Check if dollar signs are
everywhere where they should be.

"murkaboris" wrote:

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" wrote:

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" wrote:

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.

Boris,

data in my example a
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" wrote:

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" wrote:

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, D2:D9, E2:E9 when copying down. Check if dollar signs are
everywhere where they should be.

"murkaboris" wrote:

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" wrote:

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" wrote:

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.