| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to cause the value in the denominator change with changing row
I'd like the value of the denominator to change as I key in each day sales automatically. =SUM(C31/6*23) in this example 6 is the day of the month and 23 is the number of business days for the month. Is there a way to define 23 as a formula to count the number of business days and return the value 23? Is there a way for the value in the position 6 is in to change as I key in daily sales? I've attached the spreadsheet for help. thank you STORE MONTHLY TOTAL MARCH ABC ABC DATE DAILY MTD DAILY MTD 1 12,747.71 12,747.71 3,266.29 3,266.29 2 9,319.74 22,067.45 2,871.36 6,137.65 3 16,619.81 38,687.26 5,073.21 11,210.86 6 20,166.18 58,853.44 5,999.47 17,210.33 7 14,562.88 73,416.32 4,376.16 21,586.49 73,416.32 73,416.32 21,586.49 21,586.49 8 29,382.66 102,798.98 6,852.76 28,439.25 9 0.00 102,798.98 0.00 28,439.25 10 0.00 102,798.98 0.00 28,439.25 13 0.00 102,798.98 0.00 28,439.25 14 0.00 102,798.98 0.00 28,439.25 29,382.66 102,798.98 6,852.76 28,439.25 15 0.00 102,798.98 0.00 28,439.25 16 0.00 102,798.98 0.00 28,439.25 17 0.00 102,798.98 0.00 28,439.25 20 0.00 102,798.98 0.00 28,439.25 21 0.00 102,798.98 0.00 28,439.25 0.00 102,798.98 0.00 28,439.25 22 0.00 102,798.98 0.00 28,439.25 23 0.00 102,798.98 0.00 28,439.25 24 0.00 102,798.98 0.00 28,439.25 27 0.00 102,798.98 0.00 28,439.25 28 0.00 102,798.98 0.00 28,439.25 0.00 102,798.98 0.00 28,439.25 29 0.00 102,798.98 0.00 28,439.25 30 0.00 102,798.98 0.00 28,439.25 31 0.00 102,798.98 0.00 28,439.25 TOTAL 102,798.98 28,439.25 "SPEED" 394,062.76 109,017.13 GOALS ABC GS$: 331,793 ABC GP$: 106,279 Daily GP$ avg. required to meet monthly GP$ goal 4,620.83 Daily GP$ avg. to date: 4,739.88 Last year 3/05 Sales$ 293,622 3/05 GP$ 95,566 +-------------------------------------------------------------------+ |Filename: Store MPR.zip | |Download: http://www.excelforum.com/attachment.php?postid=4466 | +-------------------------------------------------------------------+ -- nander ------------------------------------------------------------------------ nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156 View this thread: http://www.excelforum.com/showthread...hreadid=522968 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to cause the value in the denominator change with changing row
Look at the NETWORKDAYS function (which requires the Analysis Toolpack
Add-In....Tools==Add-ins==Analysis Toolpak) This will calculate the working days between two dates so will give working days in a month and the working days from start of month to current sales date. HTH "nander" wrote: I'd like the value of the denominator to change as I key in each day sales automatically. =SUM(C31/6*23) in this example 6 is the day of the month and 23 is the number of business days for the month. Is there a way to define 23 as a formula to count the number of business days and return the value 23? Is there a way for the value in the position 6 is in to change as I key in daily sales? I've attached the spreadsheet for help. thank you STORE MONTHLY TOTAL MARCH ABC ABC DATE DAILY MTD DAILY MTD 1 12,747.71 12,747.71 3,266.29 3,266.29 2 9,319.74 22,067.45 2,871.36 6,137.65 3 16,619.81 38,687.26 5,073.21 11,210.86 6 20,166.18 58,853.44 5,999.47 17,210.33 7 14,562.88 73,416.32 4,376.16 21,586.49 73,416.32 73,416.32 21,586.49 21,586.49 8 29,382.66 102,798.98 6,852.76 28,439.25 9 0.00 102,798.98 0.00 28,439.25 10 0.00 102,798.98 0.00 28,439.25 13 0.00 102,798.98 0.00 28,439.25 14 0.00 102,798.98 0.00 28,439.25 29,382.66 102,798.98 6,852.76 28,439.25 15 0.00 102,798.98 0.00 28,439.25 16 0.00 102,798.98 0.00 28,439.25 17 0.00 102,798.98 0.00 28,439.25 20 0.00 102,798.98 0.00 28,439.25 21 0.00 102,798.98 0.00 28,439.25 0.00 102,798.98 0.00 28,439.25 22 0.00 102,798.98 0.00 28,439.25 23 0.00 102,798.98 0.00 28,439.25 24 0.00 102,798.98 0.00 28,439.25 27 0.00 102,798.98 0.00 28,439.25 28 0.00 102,798.98 0.00 28,439.25 0.00 102,798.98 0.00 28,439.25 29 0.00 102,798.98 0.00 28,439.25 30 0.00 102,798.98 0.00 28,439.25 31 0.00 102,798.98 0.00 28,439.25 TOTAL 102,798.98 28,439.25 "SPEED" 394,062.76 109,017.13 GOALS ABC GS$: 331,793 ABC GP$: 106,279 Daily GP$ avg. required to meet monthly GP$ goal 4,620.83 Daily GP$ avg. to date: 4,739.88 Last year 3/05 Sales$ 293,622 3/05 GP$ 95,566 +-------------------------------------------------------------------+ |Filename: Store MPR.zip | |Download: http://www.excelforum.com/attachment.php?postid=4466 | +-------------------------------------------------------------------+ -- nander ------------------------------------------------------------------------ nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156 View this thread: http://www.excelforum.com/showthread...hreadid=522968 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to cause the value in the denominator change with changing row
One way ..
In B33: =C31/SUMPRODUCT(($A$4:$A$30<"")*(B4:B30<0))*COUNT($A$ 4:$A$30) [ replaces: =SUM(C31/6*23) ] Similarly, In D33: =E31/SUMPRODUCT(($A$4:$A$30<"")*(D4:D30<0))*COUNT($A$ 4:$A$30) [ replaces: =SUM(E31/6*23) ] P/s: Noted you are using a number of superfluous SUM(...) as well in eg: C4, C5, ... Just use: =B4, instead of =SUM(B4), use: =C4+B5, instead of =SUM(C4+B5), ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "nander" wrote in message ... I'd like the value of the denominator to change as I key in each day sales automatically. =SUM(C31/6*23) in this example 6 is the day of the month and 23 is the number of business days for the month. Is there a way to define 23 as a formula to count the number of business days and return the value 23? Is there a way for the value in the position 6 is in to change as I key in daily sales? I've attached the spreadsheet for help. thank you STORE MONTHLY TOTAL MARCH ABC ABC DATE DAILY MTD DAILY MTD 1 12,747.71 12,747.71 3,266.29 3,266.29 2 9,319.74 22,067.45 2,871.36 6,137.65 3 16,619.81 38,687.26 5,073.21 11,210.86 6 20,166.18 58,853.44 5,999.47 17,210.33 7 14,562.88 73,416.32 4,376.16 21,586.49 73,416.32 73,416.32 21,586.49 21,586.49 8 29,382.66 102,798.98 6,852.76 28,439.25 9 0.00 102,798.98 0.00 28,439.25 10 0.00 102,798.98 0.00 28,439.25 13 0.00 102,798.98 0.00 28,439.25 14 0.00 102,798.98 0.00 28,439.25 29,382.66 102,798.98 6,852.76 28,439.25 15 0.00 102,798.98 0.00 28,439.25 16 0.00 102,798.98 0.00 28,439.25 17 0.00 102,798.98 0.00 28,439.25 20 0.00 102,798.98 0.00 28,439.25 21 0.00 102,798.98 0.00 28,439.25 0.00 102,798.98 0.00 28,439.25 22 0.00 102,798.98 0.00 28,439.25 23 0.00 102,798.98 0.00 28,439.25 24 0.00 102,798.98 0.00 28,439.25 27 0.00 102,798.98 0.00 28,439.25 28 0.00 102,798.98 0.00 28,439.25 0.00 102,798.98 0.00 28,439.25 29 0.00 102,798.98 0.00 28,439.25 30 0.00 102,798.98 0.00 28,439.25 31 0.00 102,798.98 0.00 28,439.25 TOTAL 102,798.98 28,439.25 "SPEED" 394,062.76 109,017.13 GOALS ABC GS$: 331,793 ABC GP$: 106,279 Daily GP$ avg. required to meet monthly GP$ goal 4,620.83 Daily GP$ avg. to date: 4,739.88 Last year 3/05 Sales$ 293,622 3/05 GP$ 95,566 +-------------------------------------------------------------------+ |Filename: Store MPR.zip | |Download: http://www.excelforum.com/attachment.php?postid=4466 | +-------------------------------------------------------------------+ -- nander ------------------------------------------------------------------------ nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156 View this thread: http://www.excelforum.com/showthread...hreadid=522968 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to cause the value in the denominator change with changing row
In cell E38 the value should be 4739.87 but the formula =E31/SUMPRODUCT(($A$4:$A$30<"")*(D4:D30<0))*COUNT($A$ 4:$A$30) returns the value 109,017.13. The value of the denominator increments by 1 each day GM$ are keyed in column D. MARCH ABC ABC DATE DAILY MTD DAILY MTD 3/1/2006 12,747.71 12,747.71 3,266.29 3,266.29 2 9,319.74 22,067.45 2,871.36 6,137.65 3 16,619.81 38,687.26 5,073.21 11,210.86 6 20,166.18 58,853.44 5,999.47 17,210.33 7 14,562.88 73,416.32 4,376.16 21,586.49 73,416.32 73,416.32 21,586.49 21,586.49 8 29,382.66 102,798.98 6,852.76 28,439.25 9 0.00 102,798.98 0.00 28,439.25 10 0.00 102,798.98 0.00 28,439.25 13 0.00 102,798.98 0.00 28,439.25 14 0.00 102,798.98 0.00 28,439.25 29,382.66 102,798.98 6,852.76 28,439.25 15 0.00 102,798.98 0.00 28,439.25 16 0.00 102,798.98 0.00 28,439.25 17 0.00 102,798.98 0.00 28,439.25 20 0.00 102,798.98 0.00 28,439.25 21 0.00 102,798.98 0.00 28,439.25 0.00 102,798.98 0.00 28,439.25 22 0.00 102,798.98 0.00 28,439.25 23 0.00 102,798.98 0.00 28,439.25 24 0.00 102,798.98 0.00 28,439.25 27 0.00 102,798.98 0.00 28,439.25 28 0.00 102,798.98 0.00 28,439.25 0.00 102,798.98 0.00 28,439.25 29 0.00 102,798.98 0.00 28,439.25 30 0.00 102,798.98 0.00 28,439.25 3/31/2006 0.00 102,798.98 0.00 28,439.25 TOTAL 102,798.98 28,439.25 23 "SPEED" 394,062.76 109,017.13 GOALS KNOX GS$: 331,793 KNOX GP$: 106,279 Daily GP$ avg. required to meet monthly GP$ goal 4,620.83 Daily GP$ avg. to date: 109,017.13 Last year 3/05 Sales$ 293,622 3/05 GP$ 95,566 +-------------------------------------------------------------------+ |Filename: Store MPR.zip | |Download: http://www.excelforum.com/attachment.php?postid=4490 | +-------------------------------------------------------------------+ -- nander ------------------------------------------------------------------------ nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156 View this thread: http://www.excelforum.com/showthread...hreadid=522968 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to cause the value in the denominator change with changing row
Delete the existing zeros from all input cells within cols B and D
(where inputs would be progressively made) Then we could use the following revised formulas: In B33: =C31/(COUNTIF(B4:B30,"<")-COUNTIF(A4:A30,""))*COUNT($A$4:$A$30) In D33: =E31/(COUNTIF(D4:D30,"<")-COUNTIF(A4:A30,""))*COUNT($A$4:$A$30) and in E38: =E31/(COUNTIF(D4:D30,"<")-COUNTIF(A4:A30,"")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "nander" wrote in message ... In cell E38 the value should be 4739.87 but the formula =E31/SUMPRODUCT(($A$4:$A$30<"")*(D4:D30<0))*COUNT($A$ 4:$A$30) returns the value 109,017.13. The value of the denominator increments by 1 each day GM$ are keyed in column D. MARCH ABC ABC DATE DAILY MTD DAILY MTD 3/1/2006 12,747.71 12,747.71 3,266.29 3,266.29 2 9,319.74 22,067.45 2,871.36 6,137.65 3 16,619.81 38,687.26 5,073.21 11,210.86 6 20,166.18 58,853.44 5,999.47 17,210.33 7 14,562.88 73,416.32 4,376.16 21,586.49 73,416.32 73,416.32 21,586.49 21,586.49 8 29,382.66 102,798.98 6,852.76 28,439.25 9 0.00 102,798.98 0.00 28,439.25 10 0.00 102,798.98 0.00 28,439.25 13 0.00 102,798.98 0.00 28,439.25 14 0.00 102,798.98 0.00 28,439.25 29,382.66 102,798.98 6,852.76 28,439.25 15 0.00 102,798.98 0.00 28,439.25 16 0.00 102,798.98 0.00 28,439.25 17 0.00 102,798.98 0.00 28,439.25 20 0.00 102,798.98 0.00 28,439.25 21 0.00 102,798.98 0.00 28,439.25 0.00 102,798.98 0.00 28,439.25 22 0.00 102,798.98 0.00 28,439.25 23 0.00 102,798.98 0.00 28,439.25 24 0.00 102,798.98 0.00 28,439.25 27 0.00 102,798.98 0.00 28,439.25 28 0.00 102,798.98 0.00 28,439.25 0.00 102,798.98 0.00 28,439.25 29 0.00 102,798.98 0.00 28,439.25 30 0.00 102,798.98 0.00 28,439.25 3/31/2006 0.00 102,798.98 0.00 28,439.25 TOTAL 102,798.98 28,439.25 23 "SPEED" 394,062.76 109,017.13 GOALS KNOX GS$: 331,793 KNOX GP$: 106,279 Daily GP$ avg. required to meet monthly GP$ goal 4,620.83 Daily GP$ avg. to date: 109,017.13 Last year 3/05 Sales$ 293,622 3/05 GP$ 95,566 +-------------------------------------------------------------------+ |Filename: Store MPR.zip | |Download: http://www.excelforum.com/attachment.php?postid=4490 | +-------------------------------------------------------------------+ -- nander ------------------------------------------------------------------------ nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156 View this thread: http://www.excelforum.com/showthread...hreadid=522968 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to cause the value in the denominator change with changing row
Thank you so very much MAX!!!! -- nander ------------------------------------------------------------------------ nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156 View this thread: http://www.excelforum.com/showthread...hreadid=522968 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to cause the value in the denominator change with changing row
You're welcome, Nander !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "nander" wrote in message ... Thank you so very much MAX!!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| NOW() keeps changing | Excel Discussion (Misc queries) | |||
| Automatically insert time without changing. | Excel Discussion (Misc queries) | |||
| Formula changing | Excel Discussion (Misc queries) | |||
| Changing format of number without changing the value | Excel Discussion (Misc queries) | |||
| How do I stop excel automatically changing my date to 2005? | Excel Discussion (Misc queries) | |||
Linear Mode
