division error - excel 2016

Frank .S
Frank .S used Ask the Experts™
on
hi experts, im getting a #DIV/0! error from my formula but i cant understand why, could you please assist.
formula. =IF(R4="","",(Q4*R4)/(S4))
error. #DIV/0!

division error_1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NoahHardware Tester and Debugger

Commented:
Hi there! :)

Can you attach an example file? It doesn't have to contain your actual data, just needs to work the same way.
AlanConsultant

Commented:
Hi,

From your formula, S4 must evaluate to zero.

If you select S4 in the formula, then press F9, Excel will show you the value.  What does it show?

Once done, press Escape to return the formula to what it was before, or use Ctrl Z to undo any change.

Alan.
NoahHardware Tester and Debugger

Commented:
Yes, visually it seems like there is nothing in Cell S4, so ensure there is a correct reference or some form of number. That might be why the DIV/0 error is showing; a divide by zero error.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Frank .SBuilding Estimator

Author

Commented:
hi experts, i have attached the actual file for your reference.
zGen_calcs.xlsm
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
In the screenshot, cell S4 is blank. A blank cell is treated like 0 when used in arithmetic expressions. Since you are dividing by S4, you should expect a #DIV/0! error value when S4 is blank. You can avoid this problem with either of the following formulas:
=IF(S4="","",Q4*R4/S4)
=IF(COUNT(Q4:S4)<3,"",Q4*R4/S4)

Open in new window

NoahHardware Tester and Debugger

Commented:
@Frank .S I have taken a look at your excel and as stated by @byundt and myself, cell S5 is blank which is why the DIV/0 error appears. The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero.  There should a number, if the number in cell S5 can be 0, then you can edit your formula such that the result can also be zero if that is the case.

=IF(R5="","0",IF(OR(S5>0,ISBLANK(S5)),"0",(Q5*R5)/(S5)))

Open in new window

Frank .SBuilding Estimator

Author

Commented:
what about an if-and-or statement, as in;
if R5 ="" and/ or S5 ="","",Q5*R5/S5?
could this work? and if so, how would you write up the formula?
Frank .SBuilding Estimator

Author

Commented:
the total in cell T25 of column T, even with byundts formula still produces the following error;
=IF(T5:T24="","",SUM(T5:T24))
NoahHardware Tester and Debugger

Commented:
Do refer to this attached Excel file: zGen_calcs.xlsm

I have changed the formulas for the individual cells' calculation in Column T based on the logic quoted here:
what about an if-and-or statement, as in;
if R5 ="" and/ or S5 ="","",Q5*R5/S5?
could this work? and if so, how would you write up the formula?
=IF(OR(R11="",S11=""),"",(Q11*R11)/(S11))

Open in new window


As for @byundt's code, I put his formula into an IFERROR bracket. So if still something still shows error after that IF statement, it will show blank.
=IFERROR(IF(T5:T24="","",SUM(T5:T24)),"")

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Noah,
When I tested your suggestion for the SUM formula, it either returned 20 different values (Excel 2016/Office 365 with dynamic arrays) or a single value based on the contents of cell T5 (other Excel versions). Neither outcome seemed like what you intended.

I suspect you intended for every cell in T5:T24 to be tested, but a single value returned. If so, the following is working for me. It should be array-entered unless you have the dynamic arrays feature.
=IFERROR(IF(AND(T5:T24=""),"",SUM(T5:T24)),"")

Open in new window


Brad
NoahHardware Tester and Debugger

Commented:
@byundt Thanks for the edit :)
Frank .SBuilding Estimator

Author

Commented:
im confused because i have tried the solutions but they are not working for me, thankyou
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
i have tried the solutions but they are not working for me
That means you need to post your workbook. Not a screenshot. If we can reproduce the error, we can either fix it or give you a workaround.
Frank .SBuilding Estimator

Author

Commented:
i have attached for your ref.
zGen_calcs_value-error---total-disp.xlsm
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I put formulas in cells T5:T25
=IF(COUNT(Q5:S5)<3,"",(Q5*R5)/(S5))
=IF(SUM(T5:T24)=0,"",SUM(T5:T24))

Open in new window

zGen_calcs_value-error---total-disp.xlsm
Frank .SBuilding Estimator

Author

Commented:
thankyou for your assistance & solutions
Frank .SBuilding Estimator

Author

Commented:
sorry byundt, but for some reason now that i have just copied that formula from T5 to T6, it gives me the DIV error, and the same in T25
please assist?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Please post the workbook that has the error.

The workbook I posted had the suggested formulas in all the cells from T5 to T25, so you shouldn't have needed to copy the formula.
Frank .SBuilding Estimator

Author

Commented:
glich at my end, seems to work now... all good

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial