help with excel please

builder22

builder22

builder22

Soldato
Joined
14 Dec 2005
Posts
5,047
basically I have to enter the number 1 in a cell to avoid getting the 'cant divide by zero' message, I'd like excel to assume that if the cell is left blank then the value is 1

the formula in the cell just now is "=P5/Q5*R5" but if r5 is left blank then it returns a 'cant divide by zero' error

I tried entering "=P5/Q5*R5 and if r5=0 then r5=1" which kinda makes sense to me but I don't really know much about excel and that doesn't work, just brings back an error

I would just enter the number 1 everytime if i have to but theres about 20 rows with the formula so 1 would need to be entered on every row so the total of all the rows doesnt return the 'cant divide by zero message' too

help please :d
 
Rather than having the cell be 1 if left blank, I would use the ISERROR formula to remove the error entirely.

=IF(ISERROR(P5/Q5*R5)=TRUE,"",P5/Q5*R5)

This way, for any blank cells or cells with zero, the value returned would be a blank cell (or replace the "" with anything else).
 
Yea that would work better. Never used ISERROR before that is quite useful. I will put that into some of my worksheets now to improve the formula. lol :-)
 
Robbie G said:
Rather than having the cell be 1 if left blank, I would use the ISERROR formula to remove the error entirely.

=IF(ISERROR(P5/Q5*R5)=TRUE,"",P5/Q5*R5)

This way, for any blank cells or cells with zero, the value returned would be a blank cell (or replace the "" with anything else).
Click to expand...
That's exactly what I do. :)

Unfortunately I use this so infrequently I always have to look it up again. :D:o
 
You must log in or register to reply here.
Back
Top Bottom