This is probably simple but.........

[Vizzola]

Good morning. I want to create a spreadsheet which tracks performance to target but only shows values when the result is positive. The spreadsheet I have created shows the following by column - TARGET, ACTUAL MONTHLY CUMULATIVE, DIFFERENCE (+/- value), 7.5% of target value (assuming that target has been achieved) 10% of VALUE IN EXCESS of TARGET, COMBINED VALUE OF the 7.5% & 10%. If target isn't achieved then I don't want any value showing.

Date	Target	Actual (Daily)	Actual (cumulative)	Diff		7.5%	10.0%	Total
2nd	£800	£600	£600	-£200		£60	-£20	£40
3rd	£1,600		£600	-£1,000		£120	-£100	£20

<colgroup><col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" span="2"> <tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you show a slightly larger sample where there are a few rows where target is met and a few rows where target is not met and show the actual results as you would want them to be.

 

[Vizzola]

Welcome to the MrExcel board!

Could you show a slightly larger sample where there are a few rows where target is met and a few rows where target is not met and show the actual results as you would want them to be.

Hi Peter, thank you for reply. I am trying to create a spreadsheet that allows an operative to see how much profit share they are earning on a daily basis. They would be given a target and if that target is achieved they will receive 7.5% of target and 10% of anything (surplus) above that target. However I'm not very good at a lot of aspects of Excel and the sheet looks untidy. I have copied the full sheet for you below. If the target isn't achieved then nothing has been earned and ideally the total column would show 0.00 - If possible I would also like the shaded area to show nothing until the ACTUAL (Daily) cell is populated. Hope that makes sense.

Profit Share April 2013 - Astley
Date	Target	Actual (Daily)	Actual (cumulative)	Diff	7.5%	10.0%	Total
2nd	£800	£750	£750	£0	-£4	£0	-£4
3rd	£1,600	£675	£1,425	-£175	£120	-£18	£103
4th	£2,400	£1,100	£2,525	£125	£180	£13	£193
5th	£3,200	£825	£3,350	£150	£240	£15	£255
6th	£4,000	£240	£3,590	-£410	£300	-£41	£259
8th	£4,800		£3,590	-£1,210	£360	-£121	£239
9th	£5,600		£3,590	-£2,010	£420	-£201	£219
10th	£6,400		£3,590	-£2,810	£480	-£281	£199
11th	£7,200		£3,590	-£3,610	£540	-£361	£179
12th	£8,000		£3,590	-£4,410	£600	-£441	£159
13th	£8,800		£3,590	-£5,210	£660	-£521	£139
15th	£9,600		£3,590	-£6,010	£720	-£601	£119
16th	£10,400		£3,590	-£6,810	£780	-£681	£99
17th	£11,200		£3,590	-£7,610	£840	-£761	£79
18th	£12,000		£3,590	-£8,410	£900	-£841	£59
19th	£12,800		£3,590	-£9,210	£960	-£921	£39
20th	£13,600		£3,590	-£10,010	£1,020	-£1,001	£19
22nd	£14,400		£3,590	-£10,810	£1,080	-£1,081	-£1
23rd	£15,200		£3,590	-£11,610	£1,140	-£1,161	-£21
24th	£16,000		£3,590	-£12,410	£1,200	-£1,241	-£41
25th	£16,800		£3,590	-£13,210	£1,260	-£1,321	-£61
26th	£17,600		£3,590	-£14,010	£1,320	-£1,401	-£81
27th	£18,400		£3,590	-£14,810	£1,380	-£1,481	-£101
29th	£19,200		£3,590	-£15,610	£1,440	-£1,561	-£121
30th	£20,000		£3,590	-£16,410	£1,500	-£1,641	-£141
	£20,000

<colgroup><col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" span="2"> <tbody>
</tbody>

 

[Peter_SSs]

I asked for the table as you want it to appear, not as you don't want it to appear.

OK, I understand about omitting the grey area, but because you didn't show me what you wanted I'm unsure about ..

- why the first Diff is 0? It doesn't seem to agree with how the Diff shown in post #1 would have been calculated. In fact I can't see how any of that first row is calculated.

- do you want any of the final 4 columns populated if the target has not been achieved? Which one(s)?

- can you confirm just how the Total column is calculated?

- could you show the table again, down to the first row of grey would do, with the actual results (checked carefully) that you want, including blanks?

 

[Vizzola]

I asked for the table as you want it to appear, not as you don't want it to appear.

OK, I understand about omitting the grey area, but because you didn't show me what you wanted I'm unsure about ..

- why the first Diff is 0? It doesn't seem to agree with how the Diff shown in post #1 would have been calculated. In fact I can't see how any of that first row is calculated.

- do you want any of the final 4 columns populated if the target has not been achieved? Which one(s)?

- can you confirm just how the Total column is calculated?

- could you show the table again, down to the first row of grey would do, with the actual results (checked carefully) that you want, including blanks?

Sorry Peter, didn't think I was making myself clear. If they don't achieve target they get nothing. In Answer to your question - In the example below the 1st row is calculated as Target - Actually achieved - The Actual (cumulative) is just a running total of month so in cell D4 I have entered the formula =C4+C3, in cell D5 =D4+C5 and so on. The DIFF is the difference between running target and running actual. If the the DIFF is a negative value (not achieved target) then the following cells 7.5%, 10% and TOTAL will show 0.00 (or can be totally blank if easier). When target is achieved or exceeded then columns M, N & O become populated with values as illustrated. No, I don't want any of the final 4 columns populated (unless as previously mentioned it is easier to fill with 0.00). The TOTAL column is just the combined values of columns M & N. Just to clarify - all cells to remain blank until a figure is entered in the Actual (daily) is populated. Many thanks for your patience.

Profit Share April 2013 - Astley
Date	Target	Actual (Daily)	Actual (cumulative)	Diff	7.5%	10.0%	Total
2nd	£800	£750	£750	-£50	£0	£0	£0
3rd	£1,600	£675	£1,425	-£175	£0	£0	£0
4th	£2,400	£1,000	£2,425	£25	£180	£3	£183
5th	£3,200	£600	£3,025	-£175	£0	£0	£0
6th	£4,000	£2,000	£5,025	£1,025	£300	£103	£403
8th	£4,800
9th	£5,600
10th	£6,400
11th	£7,200
12th	£8,000

<colgroup><col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" span="2"> <tbody>
</tbody>

 

[Peter_SSs]

Thanks, that's clearer.
Try each of these formulas copied down.

Excel Workbook
	A	B	C	D	E	F	G	H
2	Date	Target	Actual (Daily)	Actual (cumulative)	Diff	7.50%	10.00%	Total
3	2nd	800	750	750	-50
4	3rd	1,600	675	1425	-175
5	4th	2,400	1,000	2425	25	180	2.5	182.5
6	5th	3,200	600	3025	-175
7	6th	4,000	2000	5025	1025	300	102.5	402.5
8	8th	4,800
9	9th	5,600
Profit Share

 

[Vizzola]

Thanks, that's clearer.
Try each of these formulas copied down.

Profit Share

*	A	B	C	D	E	F	G	H
2	Date	Target	Actual (Daily)	Actual (cumulative)	Diff	7.50%	10.00%	Total
3	2nd	800	750	750	-50	*	*	*
4	3rd	1,600	675	1425	-175	*	*	*
5	4th	2,400	1,000	2425	25	180	2.5	182.5
6	5th	3,200	600	3025	-175	*	*	*
7	6th	4,000	2000	5025	1025	300	102.5	402.5
8	8th	4,800	*	*	*	*	*	*
9	9th	5,600	*	*	*	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 56px;"><col style="width: 63px;"><col style="width: 105px;"><col style="width: 133px;"><col style="width: 56px;"><col style="width: 82px;"><col style="width: 63px;"><col style="width: 77px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D3 =IF(C3="","",SUM(C$3:C3)) E3 =IF(C3="","",D3-B3) F3 =IF(C3="","",IF(E3<0,"",F$2*B3)) G3 =IF(C3="","",IF(E3<0,"",G$2*E3)) H3 =IF(C3="","",IF(E3<0,"",F3+G3)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Many thanks Peter..... You are good! That is near perfect for my needs. Not really anyway I can repay you for your help but it is very much appreciated. Graeme