Calculate target goal by value

[Zishan97]

Hi All,

I hope someone can help me with the correct formula for my excel sheet. I've set my weekly target to £450 for the week and it reflects on the 'Acheived/Missed Target' row but I'm trying for it to show minus and positive value. For example, right now I haven't earned anything so it should be -£450 and if I've made £500 it should be +£50. I hope someone can help me with this.

Thank you all,

Zishan

Screenshot 2020-07-20 at 16.55.36.png

[Pepe Le Mokko]

Hi
a sheet would be much easier to use than pictures. See yellow banner for how-to

[Zishan97]

Ok, I think I've attached it.

[JeteMc]

I could be missing something, however it seems that the following does what you want:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Zishan97]

I could be missing something, however it seems that the following does what you want:

Please Login or Register  to view this content.

Let us know if you have any questions.

It calculates it but it doesn’t calculate as minus -£450 for example..

So for example I require £450 to hit target so I want it to display as -£450 and it goes down as I update it and if I over achieve the target it should show as +£50 (for example) but right now it’s in red and in brackets

[JeteMc]

Try applying the following custom formatting to cell F64: _("+£"* #,##0.00_);_("-£"* #,##0.00;_("£"* "-"??_);_(@_)
Let us know if you have any questions.

[Zishan97]

Hi,

That didn’t seem to do the trick unfortunately. It still shows as +£500 when it should be -£500 as I haven’t reached £500. If I exceed my target of £500 for example it should show much I have I over achieved for example, if the target is £500, and I made £550 for the week it should display as +£50 if I’m £50 away from £500 it should display as -£50. Hope that helps.

[JeteMc]

Here is the file with cell F64 displaying -£500. If you type 75 into each of cells F51:F57 then cell F64 will display +£25.
Let us know if you have any questions.

[Zishan97]

Thank you so much!!! Appreciate that a lot

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.