I'm trying to calulate the daily sales to date in the attached
spreadsheet cell E39. With this formula
=C33/(COUNTIF(B4:B31,"<")-COUNTIF(A4:A31,"")) This formulae returns
the value 2703.52. However if you divide 64884.57 by 6 the value is
10,814.095. How should the formula be changed?
A B C
JUNE DHN GS$
DATE DAILY MTD
6/1/2006 18,635.51 18,635.51
6/2/2006 7,248.36 25,883.87
6/5/2006 9,064.83 34,948.70
6/6/2006 10,954.83 45,903.53
6/7/2006 10,495.26 56,398.79
56,398.79 56,398.79
6/8/2006 8,485.78 64,884.57
6/9/2006 0.00 64,884.57


+-------------------------------------------------------------------+
|Filename: excel forum.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4868 |
+-------------------------------------------------------------------+

--
nander
------------------------------------------------------------------------
nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156
View this thread: http://www.excelforum.com/showthread...hreadid=550391

Hi!

if you divide 64884.57 by 6 the value is 10,814.095
=C33/(COUNTIF(B4:B31,"<")-COUNTIF(A4:A31,""))

Your formula isn't dividing by 6, it's dividing by 24

COUNTIF(B4:B31,"<") = 28

COUNTIF(A4:A31,"") = 4

=C33/(28-4)

Not sure what your logic is with this:

COUNTIF(B4:B31,"<")

That counts all cells in the range that aren't empty.

If you only wanted the count of cells that <0 then that total would be 8.
But 8-4 still does not equal 6.

So, how are you arriving at a divisor of 6?

Biff

"nander" wrote in
message ...

I'm trying to calulate the daily sales to date in the attached
spreadsheet cell E39. With this formula
=C33/(COUNTIF(B4:B31,"<")-COUNTIF(A4:A31,"")) This formulae returns
the value 2703.52. However if you divide 64884.57 by 6 the value is
10,814.095. How should the formula be changed?
A B C
JUNE DHN GS$
DATE DAILY MTD
6/1/2006 18,635.51 18,635.51
6/2/2006 7,248.36 25,883.87
6/5/2006 9,064.83 34,948.70
6/6/2006 10,954.83 45,903.53
6/7/2006 10,495.26 56,398.79
56,398.79 56,398.79
6/8/2006 8,485.78 64,884.57
6/9/2006 0.00 64,884.57


+-------------------------------------------------------------------+
|Filename: excel forum.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4868 |
+-------------------------------------------------------------------+

--
nander
------------------------------------------------------------------------
nander's Profile:
http://www.excelforum.com/member.php...fo&userid=6156
View this thread: http://www.excelforum.com/showthread...hreadid=550391

Biff, The formula was given to me a few months back in another post and
it works for the GP$ goal. I want to to the same for the Sales $ goal.
So I'll have to claim some ingnorance because I don't really understand
the logic. I'm adding the daily sales figure each day and want to see if
we are reaching the daily target. Are you inclined to open the file?


--
nander
------------------------------------------------------------------------
nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156
View this thread: http://www.excelforum.com/showthread...hreadid=550391

*__Should_I_remove_lines_9,_15,_21,_32?__*Your formula isn't dividing by
6, it's dividing by 24

It should not divide by 24 it should divide by the number of net
working days for the month. Which is 22 days Formula
=NETWORKDAYS(A4,A31) found in cell B34.

COUNTIF(B4:B31,"<") = 28

COUNTIF(A4:A31,"") = 4

=C33/(28-4)

Not sure what your logic is with this:

COUNTIF(B4:B31,"<")

That counts all cells in the range that aren't empty.

If you only wanted the count of cells that <0 then that total would be
8.
But 8-4 still does not equal 6.

So, how are you arriving at a divisor of 6? 6 is the number of business
days thus far this month that have sales data keyed in column B.

Biff

"nander" wrote
in
message ...

I'm trying to calulate the daily sales to date in the attached
spreadsheet cell E39. With this formula
=C33/(COUNTIF(B4:B31,"<")-COUNTIF(A4:A31,"")) This formulae returns
the value 2703.52. However if you divide 64884.57 by 6 the value is
10,814.095. How should the formula be changed?
A B C
JUNE DHN GS$
DATE DAILY MTD
6/1/2006 18,635.51 18,635.51
6/2/2006 7,248.36 25,883.87
6/5/2006 9,064.83 34,948.70
6/6/2006 10,954.83 45,903.53
6/7/2006 10,495.26 56,398.79
56,398.79 56,398.79
6/8/2006 8,485.78 64,884.57
6/9/2006 0.00 64,884.57



+-------------------------------------------------------------------+
|Filename: excel forum.zip
|
|Download: http://www.excelforum.com/attachment.php?postid=4868
|

+-------------------------------------------------------------------+

--
nander

------------------------------------------------------------------------
nander's Profile:
http://www.excelforum.com/member.php...fo&userid=6156
View this thread:
http://www.excelforum.com/showthread...hreadid=550391



--
nander
------------------------------------------------------------------------
nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156
View this thread: http://www.excelforum.com/showthread...hreadid=550391

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=AVERAGE(IF((ISNUMBER(A4:A32))*(B4:B32<0),B4:B32) )

Returns: 10814.095

Biff

"nander" wrote in
message ...

*__Should_I_remove_lines_9,_15,_21,_32?__*Your formula isn't dividing by
6, it's dividing by 24

It should not divide by 24 it should divide by the number of net
working days for the month. Which is 22 days Formula
=NETWORKDAYS(A4,A31) found in cell B34.

COUNTIF(B4:B31,"<") = 28

COUNTIF(A4:A31,"") = 4

=C33/(28-4)

Not sure what your logic is with this:

COUNTIF(B4:B31,"<")

That counts all cells in the range that aren't empty.

If you only wanted the count of cells that <0 then that total would be
8.
But 8-4 still does not equal 6.

So, how are you arriving at a divisor of 6? 6 is the number of business
days thus far this month that have sales data keyed in column B.

Biff

"nander" wrote
in
message ...

I'm trying to calulate the daily sales to date in the attached
spreadsheet cell E39. With this formula
=C33/(COUNTIF(B4:B31,"<")-COUNTIF(A4:A31,"")) This formulae returns
the value 2703.52. However if you divide 64884.57 by 6 the value is
10,814.095. How should the formula be changed?
A B C
JUNE DHN GS$
DATE DAILY MTD
6/1/2006 18,635.51 18,635.51
6/2/2006 7,248.36 25,883.87
6/5/2006 9,064.83 34,948.70
6/6/2006 10,954.83 45,903.53
6/7/2006 10,495.26 56,398.79
56,398.79 56,398.79
6/8/2006 8,485.78 64,884.57
6/9/2006 0.00 64,884.57



+-------------------------------------------------------------------+
|Filename: excel forum.zip
|
|Download: http://www.excelforum.com/attachment.php?postid=4868
|

+-------------------------------------------------------------------+

--
nander

------------------------------------------------------------------------
nander's Profile:
http://www.excelforum.com/member.php...fo&userid=6156
View this thread:
http://www.excelforum.com/showthread...hreadid=550391



--
nander
------------------------------------------------------------------------
nander's Profile:
http://www.excelforum.com/member.php...fo&userid=6156
View this thread: http://www.excelforum.com/showthread...hreadid=550391

Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:
=AVERAGE(IF((ISNUMBER(A4:A32))*(B4:B32<0),B4:B32) )

Let's make that more robust: (still array entered)

=IF(SUM(B4:B32),AVERAGE(IF((ISNUMBER(A4:A32))*(B4: B32<0),B4:B32)),"")

Biff

"Biff" wrote in message
...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=AVERAGE(IF((ISNUMBER(A4:A32))*(B4:B32<0),B4:B32) )

Returns: 10814.095

Biff

"nander" wrote in
message ...

*__Should_I_remove_lines_9,_15,_21,_32?__*Your formula isn't dividing by
6, it's dividing by 24

It should not divide by 24 it should divide by the number of net
working days for the month. Which is 22 days Formula
=NETWORKDAYS(A4,A31) found in cell B34.

COUNTIF(B4:B31,"<") = 28

COUNTIF(A4:A31,"") = 4

=C33/(28-4)

Not sure what your logic is with this:

COUNTIF(B4:B31,"<")

That counts all cells in the range that aren't empty.

If you only wanted the count of cells that <0 then that total would be
8.
But 8-4 still does not equal 6.

So, how are you arriving at a divisor of 6? 6 is the number of business
days thus far this month that have sales data keyed in column B.

Biff

"nander" wrote
in
message ...

I'm trying to calulate the daily sales to date in the attached
spreadsheet cell E39. With this formula
=C33/(COUNTIF(B4:B31,"<")-COUNTIF(A4:A31,"")) This formulae returns
the value 2703.52. However if you divide 64884.57 by 6 the value is
10,814.095. How should the formula be changed?
A B C
JUNE DHN GS$
DATE DAILY MTD
6/1/2006 18,635.51 18,635.51
6/2/2006 7,248.36 25,883.87
6/5/2006 9,064.83 34,948.70
6/6/2006 10,954.83 45,903.53
6/7/2006 10,495.26 56,398.79
56,398.79 56,398.79
6/8/2006 8,485.78 64,884.57
6/9/2006 0.00 64,884.57



+-------------------------------------------------------------------+
|Filename: excel forum.zip
|
|Download: http://www.excelforum.com/attachment.php?postid=4868
|

+-------------------------------------------------------------------+

--
nander

------------------------------------------------------------------------
nander's Profile:
http://www.excelforum.com/member.php...fo&userid=6156
View this thread:
http://www.excelforum.com/showthread...hreadid=550391



--
nander
------------------------------------------------------------------------
nander's Profile:
http://www.excelforum.com/member.php...fo&userid=6156
View this thread:
http://www.excelforum.com/showthread...hreadid=550391

Well let's see moved my cursor to the cell. Right clicked Pressed
CTRL,SHIFT,ENTER then and pasted the copied fromula in the cell. Got
#Value. Not sure what I did
wrong.=AVERAGE(IF((ISNUMBER(A4:A32))*(B4:B32<0),B 4:B32))

Also I'm not sure why the range is A4:A32 when the date column ends
with A31?


--
nander
------------------------------------------------------------------------
nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156
View this thread: http://www.excelforum.com/showthread...hreadid=550391