IS Error function in excel

[DanG]

Try this...
=SUMPRODUCT((B2:B7=A12)*(D27))/SUMPRODUCT(--(B2:B7=A12))

Note the double unary operator ("--")

What's up with the grin face?

 

[Brianwarnock]

the is because you typed : D (no space though) with smilies on , check disable smilies in text in additional options

Brian

 

[Brianwarnock]

Should have asked what -- does.

Brian

 

[DanG]

Thanks for the tip brian

I'm not really sure about the "--" as far as the technical stuff goes. I know that one of the things it does is it allows you to sum up a range even if text and numbers are in that range. Whenever I can't get sumproduct to work, I put in the "--" and it usually works.

But it is covered a little bit in the article I sent, so...RTFM (jk jk)

 

[Brianwarnock]

But it is covered a little bit in the article I sent, so...RTFM (jk jk)

Touche, where is the b#%%dy grave accent.

Have saved it, but haven't had the idle time to read it yet, only getting on at coffee breaks the boss is waving the to do list at me.

Brian

 

[rvd48]

Try this...
=SUMPRODUCT((B2:B7=A12)*(D27))/SUMPRODUCT(--(B2:B7=A12))

Note the double unary operator ("--")

What's up with the grin face?

dan, i' ve got my formula working, see post 19.

i'll stick to that now

 

[unmarkedhelicopter]

=SUMPRODUCT((B2:B7=A12)*(D27))/SUMPRODUCT(--(B2:B7=A12))

Should be :-

=SUMPRODUCT((B2:B7=A12)*(D27))/COUNTIF(B2:B7,A12))

Why make it do the work if you don't have to ?
This (depending on the size of the array) should be anything up to (approx.) 40% faster.

 

[DanG]

Should be :-

=SUMPRODUCT((B2:B7=A12)*(D27))/COUNTIF(B2:B7,A12))

Should be? rather strong choice of words I think. How about "could also be"?
Where did you pull that 40% figure from?
While you may or may not be correct in your recommendation (I really couldn't tell you) to come out and say "should be" is kind of silly in that as we all know there are many variables to consider. As an example, say the function was refering to another sheet, well then the countif would not work if the other sheet is closed, the sumproduct will.

"Should be" just seems so "I'm right and you're wrong", don't you think?

IMHO

 

[unmarkedhelicopter]

40% comes from testing the formulas with micro timers, varying sizes of arrays, running a calculate (say 100 times(per time period)), measuring the time and averaging across multiple attempts with the same formulas. (and the 40% 'maybe' unique to my machine, my installation, the way I hold my mouth and my particular corner of the galaxy !)
Given that the formulae given were always on the same sheet then countif should be the weapon of choice.
I agree that in other circumstances other expressions could be used as being more appropriate.
Yes ... re-reading the original post he does bemoan the fact that a) the formula is on a different sheet b) well actually it's on a different workbook c) well actually that workbook is closed ... no hang on ... he doesn't !!!
I'm really not into "I'm right and you're wrong" stuff unless the other guy is a complete muppet, but if you are so upset by shades of interpretation then "chase that rascally rabbit" if that fills your hearts desire.
IMHO

Array formulae are notorious bottle necks in spreadsheets, followed VERY close behind by sumproduct formulae. Anything you can do to avoid them should be done but each case should be judged on the circumstances and the user. Helper columns usually speed things up but not always, and do you hide them or not ?, what if the user deletes them 'cos he dunt knew wot thay done' ???

 

[rvd48]

hi guys, i have another issue on this. when it comes to the dividing part of the equation, it does not recognise that when a price cell is blank, it should ignore it.

i've added the formula and comment in the .xls file attached,

thanks, rvd

 

[Brianwarnock]

It is not a blank but a null value or zero length string, if it were a blank you would get a #value error in the nominator where it is highlighted in red. add the Green code to your formulae to cope with the null.

=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)*(D27<>""))))


Brian

 

[rvd48]

thanks brian, i see the green bit means anything under or over 0

tar

 

[Brianwarnock]

It means anything not null, it would accept 0, so if instead of the null there had been a 0 then it would count 2 DXE.

Brian

 

[rvd48]

thanks brian for the detail

when both price fields are empty i get a div error. i tried a if(is error formula but it doesnt seem to work properly

i've added the xls sheet and highlighted the cell with the div error, should i be using the IS error function?

cheers

 

[Brianwarnock]

Obviously if all of the prices are empty then you have a zero count of products, you could wrap an If round the denominator Sumproduct, but before you do you should ask is this making sense, should the fields be empty and if so what should I do?

Brian
PS If(sumproduct(..........)=0,?,sumproduct(.....))

 

[rvd48]

brian,

i've tried this but i keep getting an error;

=IF(SUMPRODUCT(($B$2:$B$7=$A$21)*($C$2:$C$7>=$J$18)*($C$2:C7<=$K$18))=0,0,if(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)*(D2: D7<>""="#div/0!", 0))))


the bits in red i've added on, if the price cells for DXE are blank, i dont want the #div/0! error to show up, i'd rather the cell just show 0

not sure where i've gone wrong on this?

 

[Brianwarnock]

You need to test for the divisor being 0, and not get to using it, like so
=IF(SUMPRODUCT(($B$2:$B$7=$A$21)*($C$2:$C$7>=$J$18)*($C$2:C$7<=$K$18)*(D$2$7<>""))=0,0,IF(SUMPRODUCT(($B$2:$B$7=$A$21)*($C$2:$C$7>=$J$18)*($C$2:C$7<=$K$18))=0,0,SUMPRODUCT(($B$2:$B$7=$A$21)*($C$2:$C$7>=$J$18)*($C$2:C$7<=$K$18)*($D$2:$D$7)/SUMPRODUCT(($B$2:$B$7=$A$21)*($C$2:$C$7>=$J$18)*($C$2:C$7<=$K$18)*(D$2$7<>"")))))

Brian

PS what are you studying?

It wont let me repost so replace smilie by : D but no space

 

[rvd48]

thanks brian, you've made it look simple, guess i was trying to over complicate it by referring the div/0! in my formula.



business stds at uni.

i've been in education for far too long, gcse's, a'levels and now uni. will be glad to graduate and travel around the world for a bit.

 

[Brianwarnock]

thanks brian, you've made it look simple, guess i was trying to over complicate it by referring the div/0! in my formula.
.

45yrs experience help, plus I'm a simple guy when things get complicated I duck out.

Best of luck in both your studies and travel. Travel while you can we missed out when young and its a darn sight harder now.

Brian

 

[rvd48]

tar,

i plan to go to the states this year, then east asia/aus or new zealand nxt yr.
got to make the most of my time before i get bogged down with a real job bird and responsibilities.