# Make daily sale target in excel



## Sochen (Dec 6, 2022)

Dear All,

Please help me to used the formula in excel as in the table below.

In the excel we have the daily target which already set, however if the sell is not reach the target, then variance value needs to divide by the total remaining days.
For example, on 1-Aug-2022 the daily target is $2,000, then the sale achievement is not $1,500. So the variance value to achieve is $500, then $500/6 days left, thus each day need to increase the target by $83.33. So the target in 2-Aug-2022 is $2,083.33.  and If the same in 2-Aug-2022 is $3000, then the revised target should be lower in 3-Aug-2022. However,* I want to maintain the same value as "Daily Target"*.
Please see the below table, so I want to know what formula is for this kind of table.

Book1ABCDEFGHI3Date Daily Target  Actual Sales  Achieved %  Variance  Revised Target  Revived Target
(We want) Days LeftRemark41-Aug-22$       2,000.00$            1,500.0075%$          (500.00)$                    2,000.00$                    2,000.00752-Aug-22$       2,000.00$            3,000.00150%$           916.67$                    2,083.33$                    2,083.33663-Aug-22$       2,000.00$            1,000.0050%$       (1,000.00)$                    1,916.67$                    2,000.005If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target"74-Aug-22$       2,000.00$            2,000.00100%$          (145.83)$                    2,145.83$                    2,145.83485-Aug-22$       2,000.00$            3,000.00150%$           756.94$                    2,243.06$                    2,243.06396-Aug-22$       3,000.00$            3,000.00100%$                   -$                    2,986.11$                    3,000.002If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target"107-Aug-22$       3,000.00$            3,000.00100%$                   -$                    2,972.22$                    3,000.001If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target"11$  16,000.00$       16,500.00103%Sheet1 (2)Cell FormulasRangeFormulaD4:D11D4=C4/B4E4:E10E4=C4-G4F4F4=B4G4G4=B4F5:F10F5=-SUM($E$4:E4)/H5+B5G5,G7:G8G5=-SUM($E$4:E4)/H5+B5B11:C11B11=SUM(B4:B10)Cells with Conditional FormattingCellConditionCell FormatStop If TrueD11,D4:E10Cell Value<0.95textNO


----------



## Fluff (Dec 6, 2022)

Hi & welcome to MrExcel.
How about
	
	
	
	
	
	



```
=LET(a,B5-SUM($E$4:E4)/H5,IF(a<B5,B5,a))
```


----------



## Sochen (Dec 6, 2022)

Fluff said:


> Hi & welcome to MrExcel.
> How about
> 
> 
> ...


Perfect... Thank you!


----------



## Fluff (Dec 6, 2022)

You're welcome & thanks for the feedback.


----------



## Sochen (Dec 7, 2022)

Hello Sir, that formula is not working in Office 2019. Is there any other formula which also working in excel 2019?


Fluff said:


> You're welcome & thanks for the feedback


----------



## Fluff (Dec 7, 2022)

How about
	
	
	
	
	
	



```
=IF(B5-SUM($E$4:E4)/H5<B5,B5,B5-SUM($E$4:E4)/H5)
```


----------



## Sochen (Dec 17, 2022)

Fluff said:


> How about
> 
> 
> 
> ...


My apologies for the late response. 
However, could you please check on the column G "Revised Target (We want)", what formula should be use without another helper column H?
Daily New.xlsxABCDEFGHIJ3Date Daily Target  Actual Sales  Achieved %  Variance  Revived Target  Revived Target (We want)  Balance to Achieve Days LeftRemark41-Aug-22$               2,000.00$               2,100.00105%$          100.00$                                    2,000.00$                                    2,000.00752-Aug-22$               2,000.00$               1,000.0050%$   (1,000.00)$                                    2,000.00$                                    2,000.00$                             16.676If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target"63-Aug-22$               2,000.00$               1,500.0075%$        (680.00)$                                    2,180.00$                                    2,183.33$                        (200.00)5The daily target from 3-Aug until 7-Aug should be increased if the daily archivement didn't hit the daily target74-Aug-22$               2,000.00$               3,000.00150%$          605.00$                                    2,395.00$                                    2,353.33$                        (170.00)4The daily target from 3-Aug until 7-Aug should be increased if the daily archivement didn't hit the daily target85-Aug-22$               2,000.00$               3,500.00175%$     1,175.00$                                    2,325.00$                                    2,151.67$                          201.67396-Aug-22$               3,000.000% $                                    3,000.00$                                    3,000.00$                          587.502107-Aug-22$               3,000.000% $                                    3,000.00$                                    3,000.00 1Sheet1 (4)Cell FormulasRangeFormulaD4:D10D4=C4/B4E4:E10E4=IF(OR(C4="",F4=""),"",C4-F4)F4:F10F4=IF(B4-SUM($E$3:E3)/I4<B4,B4,B4-SUM($E$3:E3)/I4)G4G4=B4G5:G10G5=IF(B5-SUM($E$3:E4)/I5<B5,B5,B5+(-SUM($H$5:H5)))H5:H10H5=IF(OR(E4="",I5=""),"",E4/I5)Cells with Conditional FormattingCellConditionCell FormatStop If TrueD11,D4:E10Cell Value<0.95textNO


----------



## Fluff (Dec 17, 2022)

What's wrong with using the helper column?


----------



## Sochen (Dec 17, 2022)

Sochen said:


> My apologies for the late response.
> However, could you please check on the column G "Revised Target (We want)", what formula should be use without another helper column H?
> Daily New.xlsxABCDEFGHIJ3Date Daily Target  Actual Sales  Achieved %  Variance  Revived Target  Revived Target (We want)  Balance to Achieve Days LeftRemark41-Aug-22$               2,000.00$               2,100.00105%$          100.00$                                    2,000.00$                                    2,000.00752-Aug-22$               2,000.00$               1,000.0050%$   (1,000.00)$                                    2,000.00$                                    2,000.00$                             16.676If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target"63-Aug-22$               2,000.00$               1,500.0075%$        (680.00)$                                    2,180.00$                                    2,183.33$                        (200.00)5The daily target from 3-Aug until 7-Aug should be increased if the daily archivement didn't hit the daily target74-Aug-22$               2,000.00$               3,000.00150%$          605.00$                                    2,395.00$                                    2,353.33$                        (170.00)4The daily target from 3-Aug until 7-Aug should be increased if the daily archivement didn't hit the daily target85-Aug-22$               2,000.00$               3,500.00175%$     1,175.00$                                    2,325.00$                                    2,151.67$                          201.67396-Aug-22$               3,000.000% $                                    3,000.00$                                    3,000.00$                          587.502107-Aug-22$               3,000.000% $                                    3,000.00$                                    3,000.00 1Sheet1 (4)Cell FormulasRangeFormulaD4:D10D4=C4/B4E4:E10E4=IF(OR(C4="",F4=""),"",C4-F4)F4:F10F4=IF(B4-SUM($E$3:E3)/I4<B4,B4,B4-SUM($E$3:E3)/I4)G4G4=B4G5:G10G5=IF(B5-SUM($E$3:E4)/I5<B5,B5,B5+(-SUM($H$5:H5)))H5:H10H5=IF(OR(E4="",I5=""),"",E4/I5)Cells with Conditional FormattingCellConditionCell FormatStop If TrueD11,D4:E10Cell Value<0.95textNO


Because. I just don’t want to use so many column.


----------



## Fluff (Dec 18, 2022)

Personally I would keep col H, it makes the formula in G far simpler.


----------



## Sochen (Dec 6, 2022)

Dear All,

Please help me to used the formula in excel as in the table below.

In the excel we have the daily target which already set, however if the sell is not reach the target, then variance value needs to divide by the total remaining days.
For example, on 1-Aug-2022 the daily target is $2,000, then the sale achievement is not $1,500. So the variance value to achieve is $500, then $500/6 days left, thus each day need to increase the target by $83.33. So the target in 2-Aug-2022 is $2,083.33.  and If the same in 2-Aug-2022 is $3000, then the revised target should be lower in 3-Aug-2022. However,* I want to maintain the same value as "Daily Target"*.
Please see the below table, so I want to know what formula is for this kind of table.

Book1ABCDEFGHI3Date Daily Target  Actual Sales  Achieved %  Variance  Revised Target  Revived Target
(We want) Days LeftRemark41-Aug-22$       2,000.00$            1,500.0075%$          (500.00)$                    2,000.00$                    2,000.00752-Aug-22$       2,000.00$            3,000.00150%$           916.67$                    2,083.33$                    2,083.33663-Aug-22$       2,000.00$            1,000.0050%$       (1,000.00)$                    1,916.67$                    2,000.005If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target"74-Aug-22$       2,000.00$            2,000.00100%$          (145.83)$                    2,145.83$                    2,145.83485-Aug-22$       2,000.00$            3,000.00150%$           756.94$                    2,243.06$                    2,243.06396-Aug-22$       3,000.00$            3,000.00100%$                   -$                    2,986.11$                    3,000.002If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target"107-Aug-22$       3,000.00$            3,000.00100%$                   -$                    2,972.22$                    3,000.001If the "Revised Target" is lesser than the "Daily target", then the revise target should be return to same value the same as "Daily Target"11$  16,000.00$       16,500.00103%Sheet1 (2)Cell FormulasRangeFormulaD4:D11D4=C4/B4E4:E10E4=C4-G4F4F4=B4G4G4=B4F5:F10F5=-SUM($E$4:E4)/H5+B5G5,G7:G8G5=-SUM($E$4:E4)/H5+B5B11:C11B11=SUM(B4:B10)Cells with Conditional FormattingCellConditionCell FormatStop If TrueD11,D4:E10Cell Value<0.95textNO


----------



## Sochen (Dec 18, 2022)

O


Fluff said:


> Personally I would keep col H, it makes the formula in G far simpler.


Ok Thank you so much Sir


----------



## Fluff (Dec 19, 2022)

You're welcome.


----------

