Daily Sales to Date calculation

[nander]

I'm trying to calulate the daily sales to date in the attache
spreadsheet cell E39. With this formul
=C33/(COUNTIF(B4:B31,"<>")-COUNTIF(A4:A31,"")) This formulae return
the value 2703.52. However if you divide 64884.57 by 6 the value i
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.5

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

 

[Biff]

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]

Biff, The formula was given to me a few months back in another post an
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 understan
the logic. I'm adding the daily sales figure each day and want to see i
we are reaching the daily target. Are you inclined to open the file

 

[nander]

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

in
message news:[email protected]...

 

[Biff]

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

 

[Biff]

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

 

[nander]

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

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

 

[Biff]

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