Targets VS Act

**Mara636** · 05-30-2024, 08:44 AM

Hello

))

SO i need to put a formula in quarterly targets fields which will calculate the amount that is needed for the target to be achieved.
Target amount is determined by percentages in each quarter which is based on business levels.

If Q1 ACT is higher than what was supposed to be achieved as 25% of the target than the target in Q2 will be lower.
If Q1 ACT is lower than 25% of the target then the remaining amount should be carried over and added on top of the Q2 target.

Target yet to be reached should be a negative number and if overachieved the number will be in positive (and green if possible).

I tried with simple add/subtract formulas and did not manage due to the number depending on actuals (i didnt know the formula)

Thanks a bunch!!!

**AliGW** · 05-30-2024, 08:45 AM

Welcome to the forum.

Please fill in your expected results, calculated manually, so we can see what we are aiming for.

**Mara636** · 05-30-2024, 09:07 AM

SO if the target is 70,000k
Q1 25% is 17,500 if Q1 has been achieved (25% of total target).

If Q1 was not achieved than the remaining balance will be added on q2 target which is again 17,500
If Q1 was overachieved than the remaining balance will be deducted from Q2 target 17,500

Hope its clear

**AliGW** · 05-30-2024, 09:13 AM

No, not really - I asked you to manually calculate results, not repeat the rules to me!

Try this in N3 copied across:

Please Login or Register  to view this content.

If that's not it, then you need to tell us what you ARE expecting in those cells (values, that is).

**Mara636** · 06-03-2024, 02:20 AM

Sorry if im not explaining myself properly

Basically the value in Targets would depend on what is inputted in Actuals.
I have uploaded now a sheet where i populated random actuals and added numbers in targets that correspond those actuals (but missing formulas).

**AliGW** · 06-03-2024, 02:31 AM

I don't understand your mathemtics - sorry.

So Target Q1 is 7500 - actual Q1 is 10000 - explain why the next target should be 10000. What is the manual calculation that you are performing to get those targets.

**Mara636** · 06-03-2024, 02:45 AM

Q1 target is 25% of 70,000 which is 17,500.
If ACT revenue generated was 10,000, the remaining 7,500 are not reached therefore they are moved on top of Q2 target.

Q2 target was also 25% of 70,000 but ACT was 15,000 so 2,500 is left plus the 7,500 from the previous quarter which was not achieved - leaving 10,000k from both quarters that is not achieved.

Moved those 10k to third quarter etc.

Thank you for trying

**AliGW** · 06-03-2024, 03:12 AM

In N3 copied across:

Please Login or Register  to view this content.

**AliGW** · 06-03-2024, 03:24 AM

It might need to be this:

Please Login or Register  to view this content.

I haven't stress tested it - that's up to you. Let me know how you get on.

**Mara636** · 06-03-2024, 04:14 AM

Yes, this works on these numbers.

I am not familiar with LET formula at all, so I'm not really able to add $ where needed for certain values not to move while dragging the formula down (ive tried

))))
Could you please just add that and i believe it will all work great

Once again, thank you sooooo much.

P.s. This LET formula will def be something i will search to learn about)

**AliGW** · 06-03-2024, 04:38 AM

Will do - later. I’m out at the moment.

**Mara636** · 06-03-2024, 04:39 AM

Anytime, i appreciate the help so much

**AliGW** · 06-03-2024, 05:46 AM

This should work:

Please Login or Register  to view this content.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

**Mara636** · 06-03-2024, 05:56 AM

It helps.

Do you mind adding it to the test attachment you attached earlier with your edit, so i don't mess up the formula on Q2, Q3 and Q4. <3
Please

**AliGW** · 06-03-2024, 05:58 AM

No need - all you need to do is copy and paste it into cell N3 and enter. You can ignore the circular reference warning. Then drag copy across and down.

**AliGW** · 06-03-2024, 06:03 AM

Slight tweak:

Please Login or Register  to view this content.

**Mara636** · 06-03-2024, 06:26 AM

Thank you, one thing confuses me and i hope i explain it right.
In this formula if Q1 is achieved and Q2 and Q3 is achieved there should be 0 in Q3 as well and remaining target stays in Q4.
Somehow even if all targets are achieved overall i have money "left" in q3 and q4.

**AliGW** · 06-03-2024, 06:29 AM

OK - this is getting too complicated for me - sorry. I did say that the testing would be down to you.

Hopefully someone else can jump in at this point.

**Mara636** · 06-03-2024, 07:11 AM

Yeah, it is way too complicated at this stage, i agree

I am also trying to figure it out and see from where is the "excess" pulled in.
Thanks either way.

**AliGW** · 06-03-2024, 07:12 AM

I think you might need helper columns, but I have put out a call for help - someone clever will find a way!

**romperstomper** · 06-04-2024, 10:20 AM

Can you clarify why P3 is 18000, rather than 10500? At that point you should have 65% of the 70,000 which is 45,500 but you actually have 35,000, so where does the 18,000 come from?

**Mara636** · 06-05-2024, 03:54 AM

You are completely right. My math was bad, been struggling with this formula too long i forgot math even.

**romperstomper** · 06-05-2024, 04:40 AM

In that case, something like =MAX(0,SUM($J2:J2)*$R3-SUM($J3:J3)) copied across 4 cells should work.

**Mara636** · 06-05-2024, 05:05 AM

Seems that works, possible to make it in minus uf target not reach therefore in plus when reached?
Sorry and thank you

**romperstomper** · 06-05-2024, 05:33 AM

Just use =- at the start. If you want it to be able to go above 0, then remove the MAX(0, function too. I assumed you wanted it capped.

**Mara636** · 06-05-2024, 06:08 AM

No, its a simple target achievement tracker - but ended up being complicated

))

Thanks a lot for helping.

**AliGW** · 06-05-2024, 06:10 AM

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Re: Targets VS Act

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If formula for more that 300 targets

[SOLVED] Each row targets a new worksheet

Bookmarks

Bookmarks

Posting Permissions