IS Error function in excel

[rvd48]

hi,

im trying to wrap my formula around the IS Error function:

=SUMPRODUCT(($J$174:$J$274)*($C$174:$C$274=$B$406)*($F$174:$F$274>=$C$393*($F$174:$F$274<=$D$393))/SUMPRODUCT((1)*($C$174:$C$274=$B$400)*($F$174:$F$274>=$C$393)*($F$174:$F$274<=$D$394)))

thats the original formula, now with is error attached:

=IF(SUMPRODUCT(($J$174:$J$274)*($C$174:$C$274=$B$406)*($F$174:$F$274)>=$C$393*($F$174:$F$274<=$D$393))<0,0,SUMPRODUCT(($J$174:$J$274)*($C$174:$C$274=$B$406)*($F$174:$F$274>=$C$393*($F$174:$F$274<=$D$393))/SUMPRODUCT((1)*($C$174:$C$274=$B$406)*($F$174:$F$274>=$C$393)*($F$174:$F$274<=$D$393))))

can somebody help me where im going wrong as i keep getting a VALUE error back?

thanks

 

[Brianwarnock]

I know I'm getting past it but those 2 formulae look different to me.

Brian

hey you've edited them, makes my post look silly

 

[rvd48]

brian, i've edited the formula and tried to use a IF statement instead, it partially works but im falling down at the <0,0, bit.

i'd like it display a zero if the value by the first bit of calculation amounts to 0. but it doesnt seem to do this

 

[Brianwarnock]

try =0 not <0

Brian

 

[rvd48]

hi brian, i tried that, but when i put =0, it the values come back as 0's.

i have attached a example worksheet to show you what i mean,

 

[Brianwarnock]

Your use of Sumproduct is outside my experience, I thought that nonnumeric entries were treated as 0 so I am wrestling with the formulae , however the If is returning TRUE so it returns a 0 as you request, if you want it to do the second condition look for -1 however then you get a #VALUE error.

Wish I could be more help.

Brian

Hm having read that it doesn't seem to make sense.

 

[Brianwarnock]

Having broken the formula down I'm beginning to understand it, damn clever this Sumproduct another few hours and I'll understand it , unfortunately have to go out now.

Cheers
Brian

 

[rvd48]

thanks brian for taking out your time to look at this sumproduct formula

 

[shades]

I think you want to have the condition "less than or equal to" (<=) rather than "less than" (<). If not, when the value is zero, you will be dividing by zero.
________
TOYOTA CENTRAL R&D LABS., INC. HISTORY

 

[Brianwarnock]

=IF(SUMPRODUCT(($D$2:$D$5)*($B$2:$B$5=$A$10)*($C$2:$C$5)>=$J$16*($C$2:$C$5<=$K$16))<>0,0,SUMPRODUCT(($D$2:$D$5)*($B$2:$B$5=$A$10)*($C$2:$C$5>=$J$16*($C$2:$C$5<=$K$16))/SUMPRODUCT((1)*($B$2:$B$5=$A$10)*($C$2:$C$5>=$J$16)*($C$2:$C$5<=$K$16))))

I think this works

I changed the condition to <> but the error #value was caused by a bracketing error.
I'll leave you to compare the 2 formulae.

Brian

BTW you have no decimal places in the ServiceClick so end up with 1 not 0.85

 

[DanG]

This use of sumproduct is amazing and I use it all the time.
For a detailed report on it check out...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[rvd48]

hi brian, i tried that formula with the <> but i still get the error when i change THX in cell B2 to some other value.

Shades, thanks for the suggestion; i still get the problem when i put in (<=). when THX is in cell 2, it comes back as 0 using (<=), even if i put in banannas in cell B2 it still gives a 0 :S

oh, its racking my brains.

btw, i have attached the excel file in the 1st post,

regards,

btw brian- liverpool culture capital? very surprising that lpool won that

 

[Brianwarnock]

hi brian, i tried that formula with the <> but i still get the error when i change THX in cell B2 to some other value.

Did you change A10 as well?

btw brian- liverpool culture capital? very surprising that lpool won that

That's you on my ignore list, not only do we play cultured football but we have more listed buildings than any city outside London, great art galleries, museums and 2 cathedrals etcetcetc

Brian

 

[rvd48]

i'd like to keep cell A10 constant. so Untill i populate the above rows with data, cells A11, B11 and C11 stay at 0, once i start to populate the rows with THX data and other models, i expect the 0's to change to figures.

if that makes sense?

from A9 to O11, im going to make a copy of this for each other model, one will go blow the other and so forth.

fair play with liverpool then, not been there myself, my hometown (leeds) wouldn't really get a shout in , but manchester would and also london.

 

[Brianwarnock]

Thanks for the link Dan I've saved it for one of those idle moments lasting several days.

Brian

 

[DanG]

Try putting this in your sample sheet cell "B11"...

=SUMPRODUCT((B2:B4=$B$2)*(C2:C4>=J16)*(C2:C4<=K16)*(D2: D4))

 

[rvd48]

thanks dan, i've worked on your formula and made a few more lines, as shown in the re-worked example (which is attached).


if you go into cell e22. i'd like it to show 0 when no DXE models are in the row of data above (instead of the current div/! error).
i've kind of done this from cells b13 to b15, and again for the DXE from cells b22 to b24, but its a crude method.

 

[Brianwarnock]

You will get a Div0 error if the model doesn't exist in both places as the If checks that both the true and false evaluate, and of course the divisor will be 0 if the model is not there.

Brian

PS I hope you don't mind me saying but I think it should be denominator not dominator, but whats en between friends.

 

[rvd48]

lol brian

by jove, i think i've cracked it!!!

using this statement:
=IF(SUMPRODUCT(($B$2:$B$7=$A$21)*($C$2:$C$7>=$J$18)*($C$2:C7<=$K$18))=0,0,SUMPRODUCT(($B$2:$B$7=$A$21)*($C$2:$C$7>=$J$18)*($C$2:C7<=$K$18)*($D$2:$D$7)/SUMPRODUCT(($B$2:$B$7=$A$21)*($C$2:$C$7>=$J$18)*($C$2:C7<=$K$18))))

it returns a 0 when their is no DXE models in the above table!!!

i've done it,

cheers brian, shades and dan. you've all helped me alot to get to this point!

 

[Brianwarnock]

We had =0 back at post#5, what has changed, my old eyes can't take anymore formulae comparisons.

Brian