|
IF Nested Function
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. |
IF Nested Function
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. |
IF Nested Function
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. |
IF Nested Function
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. |
IF Nested Function
Thank you Ashish for your efforts but it didn't work, the only answer I got
was "0" for any X value. murkaboris "Ashish Jain (Excelitems)" wrote: Hi Murkaboris, Assuming your X Value is in column A, the formula that you can use in a corresponding column is: =IF(A1<=0.2,18,IF(A1<=0.22,16,IF(A1<=0.25,14,IF(A1 <=0.28,12,IF(A1<=0.32,10,IF(A1<=0.35,8,IF(A1<=0.38 ,4,0))))))) It's a nested IF function and runs perfectly. --------------------------- Thanks and Regards Ashish Jain Technical Trainer - Excel and VBA Author - www.excelitems.com *If this post was of any help, click "Yes" below* --------------------------- "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. |
IF Nested Function
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. |
IF Nested Function
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. |
IF Nested Function
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. |
IF Nested Function
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. |
IF Nested Function
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. |
IF Nested Function
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. |
IF Nested Function
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" wrote: 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. |
IF Nested Function
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" wrote: 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. |
IF Nested Function
All rite, then. In between, I uploaded the file to web. Thx for feedback,
glad it works for u. "murkaboris" wrote: 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" wrote: 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. |
IF Nested Function
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 :-) "Alojz" wrote: All rite, then. In between, I uploaded the file to web. Thx for feedback, glad it works for u. "murkaboris" wrote: 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" wrote: 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. |
| All times are GMT +1. The time now is 03:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com