How to cause the value in the denominator change with changing row

**nander** · 03-16-2006, 12:58 AM

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

Toppers · 03-16-2006, 03:40 AM

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
>
>

Max · 03-16-2006, 03:45 AM

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" <nander.24qxly_1142485200.852@excelforum-nospam.com> wrote in
message news:nander.24qxly_1142485200.852@excelforum-nospam.com...
>
> 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
>

**nander** · 03-20-2006, 11:44 PM

In cell E38 the value should be 4739.87 (formula =E31/6) but the formula

=E31/SUMPRODUCT(($A$4:$A$30<>"")*(D4:D30<>0))*COUNT($A$4:$A$30)

returns the value 109,017.13. In the formula =E31/6 The value 6 (the denominator) increments by 1 each day the GM$ are keyed in column D. How can the formula in E38 be changed so only the range of keyed values are used. In other words if the value is 0 do not use.

Max · 03-21-2006, 01:30 AM

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" <nander.2503gy_1142912701.0598@excelforum-nospam.com> wrote in
message news:nander.2503gy_1142912701.0598@excelforum-nospam.com...
>
> 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
>

**nander** · 03-21-2006, 12:59 PM

Thank you so very much MAX!!!!

Max · 03-21-2006, 09:20 PM

You're welcome, Nander !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"nander" <nander.2514a0_1142960406.0953@excelforum-nospam.com> wrote in
message news:nander.2514a0_1142960406.0953@excelforum-nospam.com...
>
> Thank you so very much MAX!!!!