Stop SumProduct formula from rounding up

J

Judy L

Can someone help please?
I have the following formula
=SUMPRODUCT(($F$8:$BQ$27=U38)*ISNUMBER($B$8:$BM$27),$B$8:$BM$27)
and it returns a value of 177
Yet on a separate summary sheet (that is linked to read the value of that
cell) the correct value comes up 176.5
Is there a way to prevent this?
Any help would be greatly appreciated.

Thank you
 
G

Gary''s Student

The cell with the formula is not rounding up. Format the cell with the
formula to General.
 
S

Sandy Mann

Is the cell that shows 177 fornmatted to show zero decimals?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
J

Judy L

No it was formatted general.
But it is changed now, thanks. I thought it would read one decimal place
since it previewed in the formatting under general as 176.5?? And it was
working on the other sheet which is also formatted general. Unfortunately
there are a lot of 0 values and it looks busy with the 0.0 so I was hoping to
avoid that unless there was an actual decimal value.

Thanks for your help
 
D

Dave Peterson

Widen the column--or change the font size.

If the cell is formatted as general, then excel will try to display what it can.

If you change the numberformat to Number, then if the text won't fit in the
cell, you'll see ###'s.
 
J

Judy L

Thank you, you're right as usual!


Dave Peterson said:
Widen the column--or change the font size.

If the cell is formatted as general, then excel will try to display what it can.

If you change the numberformat to Number, then if the text won't fit in the
cell, you'll see ###'s.
Click to expand...
 
Top