Getting a #DIV/0! response when calculating percentages on a budget sheet

[windrain]

Hi Everyone,
I am having trouble with a #DIV/0! when calculating the percentages used and also remaining available. This happens if the Monthly Budget (column D on attached) cell is 0. Some of the catagories simply do not have a set monthly budget, but the may have an expense sometime during the year. They need to stay on the form.

On the attached worksheet,
Tab Feb - Column D is monthly budget, this amount comes from Tab "Budget Worksheet" column E.
I included this sheet only as a reference where Column D was generated from. Just in case it was useful.
Column E is where the actual amount spent is entered
Column F is the dollar amount left of the budget or if a negative, the amount spent over the budget.
Column G is % used
Column H is % remaining.

Columns J-N - Column L= Column F
Column M = Column G
Column N = Column H
I have entered what I think the correct return should be.
I have to admit that I am unsure of column N, this may be a math issue. I feel like they are right. Also for rows that have no amount in either column J or K, it would be nice to have a blank in columns M and N instead of 0%.
Also for column M, if the amount in column K is higher than column J I feel like it should be a negative because they over spent, thats what I put in row M16 . Row M19 I left it at the 121%, just to show what I mean.

OK, I hope I covered everything. I really feel like this should be easy. But my brain is done trying to figure it out. Thank you to everyon

[6StringJazzer]

I am having trouble with a #DIV/0! when calculating the percentages used and also remaining available. This happens if the Monthly Budget (column D on attached) cell is 0.

You cannot, by definition, determine a percentage of 0. That is why you get the error. All you can do it leave the result blank if the budget is 0 or blank. Example:

Please Login or Register  to view this content.

I have to admit that I am unsure of column N, this may be a math issue. I feel like they are right. Also for rows that have no amount in either column J or K, it would be nice to have a blank in columns M and N instead of 0%.

Formula above also covers that case. Alternatively, instead of blank you could show some sort of message to indicate that there was no budget.

Also for column M, if the amount in column K is higher than column J I feel like it should be a negative because they over spent, thats what I put in row M16 . Row M19 I left it at the 121%, just to show what I mean.

This is incorrect. Column M is the "% of Budget Spent." It must be 0 or positive; you cannot spend a negative amount of money. If you overspend the budget this number will be >100%. In such a case, column H "Remaining % of Budget" will be negative.

I have tweaked your file to reflect these suggestions.

[windrain]

Jeff-
Oh My Goodness! That makes so much sense, I should have known that! The formulas work perfectly.
Thank you for answering all my questions one at a time. also explaining for me. I really appreciate it.

As I have 5 different budgets with 12 months each, I am very thankful for this. That whole #DIV drives me crazy!

Stacey

[6StringJazzer]

You're welcome! Thanks for the rep, and thanks for marking your thread Solved!