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

Hi!

With the formula entered in the cell........

Select that cell
Press function key F2
Hold down both the CTRL key and the SHIFT key then hit ENTER.

When done properly Excel will enclose the formula in squiggly braces { }.
You cannot just type these braces in. You MUST use the key combination.
Also, any time you edit the formula (which is what you're doing when you
press F2) you MUST re-enter the formula using the key combination.

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

But you have an entry is B32. I'm assuming this gets updated for each new
month. Currently it's for June which has 30 days and 6/30 is the last date
in A31. If it were for July then wouldn't 7/31 be in A32? At least, that's
the impression I get just looking at the sheet but it's not MY sheet so I'm
just taking my best guess at what you're doing! If I'm wrong just change the
references!

Biff

"nander" wrote in
message ...

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