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.