Formula for # of sales days in a month?
 

I'm looking for a formula that will calculate number of sales day in a month
according to the following paramaters. For example, column A has number of
sales days as 1,2,3, etc., column B has either a number or a zero in it,
column C has either a number or a zero in it. I need the formula to look at
the values in B &C to determine what sales day it is. For example, of B10
and C10 are zero, then I need the formula to look at B9 and C9. If B9 and C9
are zero, then I need the formula to look at B8 and C8. If there is any
value other than zero in B8 and C8, then I need the formula to return the
value listed (1,2,3, etc.) from column A. Anyone know how to do this?
Thanks.

On Wed, 1 Jun 2005 10:53:19 -0700, "Kerry Rosvold" <Kerry
wrote:

I'm looking for a formula that will calculate number of sales day in a month
according to the following paramaters. For example, column A has number of
sales days as 1,2,3, etc., column B has either a number or a zero in it,
column C has either a number or a zero in it. I need the formula to look at
the values in B &C to determine what sales day it is. For example, of B10
and C10 are zero, then I need the formula to look at B9 and C9. If B9 and C9
are zero, then I need the formula to look at B8 and C8. If there is any
value other than zero in B8 and C8, then I need the formula to return the
value listed (1,2,3, etc.) from column A. Anyone know how to do this?
Thanks.

If I understand you correctly, you want to return the contents of column A that
is on the same row as the highest numbered row in Column B or Column C that
contains a 1.

If your data is in A1:C100, then the following **array-entered** formula will
do that:

=INDEX(A1:A100,MAX((B1:B100=1)*ROW(
INDIRECT("1:100")),(C1:C100=1)*ROW(
INDIRECT("1:100"))))

To **array-enter** a formula, after typing or pasting in the formula, instead
of hitting <enter, hold down <ctrl<shift while hitting <enter. Excel will
place braces {...} around the formula.


--ron

Kerry Rosvold wrote:
I'm looking for a formula that will calculate number of sales day in a month
according to the following paramaters. For example, column A has number of
sales days as 1,2,3, etc., column B has either a number or a zero in it,
column C has either a number or a zero in it. I need the formula to look at
the values in B &C to determine what sales day it is. For example, of B10
and C10 are zero, then I need the formula to look at B9 and C9. If B9 and C9
are zero, then I need the formula to look at B8 and C8. If there is any
value other than zero in B8 and C8, then I need the formula to return the
value listed (1,2,3, etc.) from column A. Anyone know how to do this?
Thanks.

E2:

=IF(B655360,65536,MATCH(2,1/(B1:B655350)))

Confirm with control+shift+enter instead of enter.

F2:

=IF(C655360,65536,MATCH(2,1/(C1:C655350)))

Confirm with control+shift+enter instead of enter.

G2:

=CHOOSE(COUNT(E2:F2)+1,"",INDEX(A:A,SUMIF(E2:F2,"< #N/A")),INDEX(A:A,MAX(E2:F2)))

which is the cell with the desired result.