division error - excel 2016

Hi there! :)

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

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.

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.

hi experts, i have attached the actual file for your reference.
zGen_calcs.xlsm

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)

Select allOpen in new window

@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)))

Select allOpen in new window

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?

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

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))

Select allOpen 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)),"")

Select allOpen in new window

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)),"")

Select allOpen in new window

Brad

@byundt Thanks for the edit :)

im confused because i have tried the solutions but they are not working for me, thankyou

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.

i have attached for your ref.
zGen_calcs_value-error---total-disp.xlsm

thankyou for your assistance & solutions

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?

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.

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