Fix #DIV/0! error

I have the following items set up in excel
------------------------------------------------------
D14 =721
D15 =391
D16 =434
=SUM(D14:D16) =1546
In the above cells I have indicated a number for something that I am tracking.
----------------------------------------------------------
=(D14/$D$17)*100
=(D15/$D$17)*100
=(D16/$D$17)*100
=SUM(E14:E16) =100
---------------------------------------------------------
My question is, if ALL the numbers I input in cells D are Zero = ''0'' I get the following error (#DIV/0!).
What would be the formula I would have to input in order for me to show either a - or a zero when ever the entries I input in a cell = or are Zero ''0'', in other words less then 1?

One other thing I forgot to ask in my original posting. The excel sheet I have created I input a number (1 – 1000,etc) in cells with # which means a month (April =D14, May =F14, June H14 – December, etc), and a percentage will show out of 100% on cells with % (E14 = %, G14 =%, I14 =%, etc due to the formula I have on these cells. See below:

Part 1 of 2

Column Column
D(#) E(%) & it’s formula on the cell

D14 =721 47 (=IF(D14=0,"0",(D14/$D$17)*100)
D15 =391 25 (=IF(D15=0,"0",(D15/$D$17)*100)
D16 =434 28 (=IF(D16=0,"0",(D16/$D$17)*100)
=SUM(D14:D16) =1546 =SUM(E14:E16) =100%


The following cells reflect a monthly number during the year which I input and their respective percentage out of 100%.

I have titled the following columns besides D14(#) E14(%), as follows F14(#) G14(%), H14(#) I14(%), J14(#) K14(%), L14(#) M14(%), N14(#) O14(%), P14(#) Q14(%), R14(#) S14(%), T14(#) U14(%) , I will enter a number(0,1 – 1000, etc) in columns with (#), and once I have entered a number, the cell will automatically calculate the percentage out of 100% for each cell with a (%).

Part 2 of 2:

Column Column
D(#) Formula E (%) Formula

D28) formula as follows =SUM(D14:D14) E28) =IF(D15=0,"0",(D14/$D$17)*100)
D29) formula as follows =SUM(D15:D15) E29) =IF(D15=0,"0",(D16/$D$17)*100)
D30) formula as follows =SUM(D16:D16) E30) =IF(D16=0,"0",(D16/$D$17)*100)
=SUM(D28:D30) =SUM(E28:E30)

The following cells reflect a running total of the monthly numbers during the year which I input in cells of Part 1 of 2 (Above) and their respective percentage out of 100%.

The same titles of (#) & (%) have been assigned to columns F28(#)G28(%), H28(#)I(%), J(#)K(%) , L(#)M(%), N(#)O(%), P(#)Q(%), R(#)S(%), T(#)U(%).

I have the following cells to be Additions as follows (See below)

Cell Formula
(#)

F28 =SUM(D14+F14)
F29 =SUM(D15+F15)
F30 =SUM(D16+F16)

(%)

G28 =IF(F14=0,"0",(F14/$F$17)*100)
G29 =IF(F15=0,"0",(F15/$F$17)*100)
G30 =IF(F16=0,"0",(F16/$F$17)*100)













(#)

H28 =SUM(D14+F14+H14)
H29 =SUM(D15+F15+H15)
H30 =SUM(D16+F16+H16)

(%)

I28 =IF(H14=0,"0",(H14/$H$17)*100)
I29 =IF(H15=0,"0",(H15/$H$17)*100)
I30 =IF(H16=0,"0",(H16/$H$17)*100)

(#)

J28 =SUM(D14+F14+H14+J14)
J29 =SUM(D15+F15+H15+J15)
J30 =SUM(D16+F16+H16+J16)

(%)

K28 =IF(J14=0,"0",(J14/$J$17)*100)
K29 =IF(J15=0,"0",(J15/$J$17)*100)
K30 =IF(J16=0,"0",(J16/$J$17)*100)

(#)

L28 =SUM(D14+F14+H14+J14+L14)
L29 =SUM(D15+F15+H15+J15+L15)
L30 =SUM(D16+F16+H16+J16+L16)

(%)

M28 =IF(L14=0,"0",(L14/$L$17)*100)
M29 =IF(L15=0,"0",(L15/$L$17)*100)
M30 =IF(L16=0,"0",(L16/$L$17)*100)

(#)

N28 =SUM(D14+F14+H14+J14+L14+N14)
N29 =SUM(D15+F15+H15+J15+L15+N15)
N30 =SUM(D16+F16+H16+J16+L16+N16)

(%)

O28 =IF(N14=0,"0",(N14/$N$17)*100)
O29 =IF(N15=0,"0",(N15/$N$17)*100)
O30 =IF(N16=0,"0",(N16/$N$17)*100)







(#)

P28 =SUM(D14+F14+H14+J14+L14+N14+P14)
P29 =SUM(D15+F15+H15+J15+L15+N15+P15)
P30 =SUM(D16+F16+H16+J16+L16+N16+P16)

(%)

Q28 =IF(P14=0,"0",(P14/$P$17)*100)
Q29 =IF(P15=0,"0",(P15/$P$17)*100)
Q30 =IF(P16=0,"0",(P16/$P$17)*100)

(#)

R28 =SUM(D14+F14+H14+J14+L14+N14+P14+R14)
R29 =SUM(D15+F15+H15+J15+L15+N15+P15+R15)
R30 =SUM(D16+F16+H16+J16+L16+N16+P16+R16)

(%)

S28 =IF(R14=0,"0",(R14/$R$17)*100)
S29 =IF(R15=0,"0",(R15/$R$17)*100)
S30 =IF(R16=0,"0",(R16/$R$17)*100)

(#)

T28 =SUM(D14+F14+H14+J14+L14+N14+P14+R14+T14)
T29 =SUM(D15+F15+H15+J15+L15+N15+P15+R15+T15)
T30 =SUM(D16+F16+H16+J16+L16+N16+P16+R16+T16)

(%)

U28 =IF(T14=0,"0",(T14/$T$17)*100)
U29 =IF(T15=0,"0",(T15/$T$17)*100)
U30 =IF(T16=0,"0",(T16/$T$17)*100)

As you see, cells F28(#) Adds cells D14(# =721) + F14(# =160), totaling =881
cells F29(#) Adds cells D15(# =391) + F15(# = 52), totaling =443
cells F30(#) Adds cells D16(# =434) + F16(# = 169), totaling =603

So, I have it set up that what ever numbers I enter in each respective cell (D#, F#, H#, J#, L#, N#, P#, R#, T#,14,15,16, etc), will equal (show) ‘’THAT’’ total number of each row & column I just indicated in this paragraph in cells D, F, H, J, L, N, P, R ,T, 28, 29, 30(Example: F28 =881, F29 =443, F30 =603, see above cells & formulas), so my other question is if I entered a ‘’0’’ on month of June (H14 =’’0’’, & H15 =’’0’’, & H16 =’’0’’), then the following cell (H28) would Add as follows =SUM(D14+F14+H14) =881 because of the formula I have there, but what I would like cells H#, J#, L#, N#, P#, R#, T#, 28, 29, 30 to show these totals only or once I have input numbers into cells in part 1 of 2, as they reflect months of the year, ie month of June/2011 – December 2011 which have not passed as of yet, we have not gone through those months yet, and I won’t know the exact numbers I need to enter in these cells will be until each respective month is over or has passed. So to clarify what I would like cells D#, F#, H#, J#, L#, N#, P#, R# ,T#, 28#, 29#, 30# is to show the current totals of D#, F#, H#, J#, L#, N#, P#, R#, T#,14,15,16, and the moths that are still not over or have passed as of yet (ie) June =#H, July =#J, August =#L, September =#N, October =#P, November =#R, December =#T,14,15,16 is for it to show ‘’0’’ until I have entered the respect numbers into those cell(s) D#, F#, H#, J#, L#, N#, P#, R#, T#,14,15,16, but to always add D#, F#, H#, J#, L#, N#, P#, R#, T#,14,15,16 accordingly, in cell(s) D#, F#, H#, J#, L#, N#, P#, R# ,T#, 28#, 29#, 30# for the current month that has ended, in this case May is over so F14, F15, F16’s total of numbers would be added to cell F28, F29,F30 accordingly to reflect Jan – May’s numbers.

I have provided as much info, maybe to much redundancy only to try to relay what I would like done, but if you need further clarification, please feel free let me know, your help is greatly appreciated, I have about 98% of what I need, I just need the other 2%.

Thank you kindly,

Hi,

I've attached a copy of what I am describing and what I would like the template to show/do.

thanks

Hi,

The original question was resolved with the information provided, suggestions & thank you. Though I have one last question, please refer above. I attached the excel sheet which I am referring to.
-----------------------------------------------------------------------------------------------------------------------------------
So to clarify what I would like cells D#, F#, H#, J#, L#, N#, P#, R# ,T#, 28#, 29#, 30# is to show the current totals of D#, F#, H#, J#, L#, N#, P#, R#, T#,14,15,16, and the moths that are still not over or have passed as of yet (ie) June =#H, July =#J, August =#L, September =#N, October =#P, November =#R, December =#T,14,15,16 is for it to show ‘’0’’ until I have entered the respect numbers into those cell(s) D#, F#, H#, J#, L#, N#, P#, R#, T#,14,15,16, but to always add D#, F#, H#, J#, L#, N#, P#, R#, T#,14,15,16 accordingly, in cell(s) D#, F#, H#, J#, L#, N#, P#, R# ,T#, 28#, 29#, 30# for the current month that has ended, in this case May is over so F14, F15, F16’s total of numbers would be added to cell F28, F29,F30 accordingly to reflect Jan – May’s numbers.

I have provided as much info, maybe to much redundancy only to try to relay what I would like done, but if you need further clarification, please feel free let me know, your help is greatly appreciated, I have about 98% of what I need, I just need the other 2%.
-----------------------------------------------------------------------------------------------------------------------------------

Thank you kindly,

Hi Haseeb,

thank you for your help. I checked the work sheet, though when I enter say 100 in the following cells #H14, H15, H16 (say as an example, 100), it updates the totals automatically in H28, H29, H30,

but when I enter for example 100(as an example) in cells #J14,J15,J16, it does not update the numbers automatically in cells #J28, J29,J30, it remains a ''0''. This is where I would like the numbers to be updated automatically once I enter a number in the monthly totals to the year to date running totals. If you try it out you will see what I mean.

Your help is Appreciated.

thank you kindly,

yes, that makes sense, that is what I asked for. Thank you again for all your help.

Much Appreciated.
Regards,