I am putting together a spreadsheet to show cost vs budget saving as a percentage
My formulas work ok until I have a £0 (Zero Value) as the budget (sometimes we do not budget for freight) - the cost vs budget saving % is then showing as 0% which it should show a negative percentage
Can anyone assist me with the formula?
Please provide example data and your formulas
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Thank you for your reply Special K
I have tried to copy a screen shot of my sheet into here but it won't let me
basically I have a budget column(G), an actual column(H), a cost vs budget saving (GBP) coumn(I) and a Cost vs budget saving % column(J)
The formula in the cost vs budget saving GBP column is =G2-H2
The formula in the cost vs budget saving % column is =IFERROR((H2-G2)/G2,0) showing the figure as a percentage
The formula works fine unless the value in column G is zero . The value in column J then shows as 0%
Is that enough to go on?
Is that enough to go on
There is a option to add attachments via a paper clip icon which doesnt work on this forum.
If you want to add attachments you need to clicl Go Advanced at the bottom and select manage Attachments.
Using this could attach a file with input and expected results?
Dear Special K I think I have now atached my file
Dear Special K I think I have now attached my file
If G2 is zero result will be #DIV/0! and IFERROR will do for you result 0
btw. You CAN'T divide anything by 0 because it will give you error #DIV/0!, that is why in Excel IFERROR fuction exist
maybe =IFERROR((G2-H2)/G2,H2) ?
Last edited by sandy666; 01-30-2017 at 11:57 AM.
Hi,Originally Posted by Matron74
Why should it show a negative percentage? Since you are showing all the savings as a percentage of the budget, if the budget is 0, what do you expect the result to be?
Don
Please remember to mark your thread 'Solved' when appropriate.
Hi I think it is the column headers that are confusing the situation here sorry
I want to show the percentage difference whether it is a saving or not. Anything under budget I want to show as a negative percentage in green to show I am that percentage under budget and anything over budget I want to show as a positive percentage value in red to show I am that percentage over budget - Is there a formula I can add to apply the positive percentage in red if the budgt is 0?
But that does not make sense. If you had nothing budgeted, what percentage have you saved/overspent? The plausible answers are 0, or plus/minus infinity.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks