Menu

Formula for # of sales days in a month?

Search this Thread

Prev

Previous Post

Next Post

Next

#2

Report Post

Ron Rosenfeld

Posts: n/a

Default

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

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
What formula converts date to month only	[email protected]	Excel Discussion (Misc queries)	1	May 16th 05 02:02 PM
Generating business days in a calendar month, EXCLUDING holidays	jacob	Excel Worksheet Functions	0	April 12th 05 04:57 AM
number of days in a month	Ryan Proudfit	Excel Worksheet Functions	8	April 7th 05 08:02 PM
Days of sales in inventory template	jr	Setting up and Configuration of Excel	0	March 14th 05 04:11 PM
formula to calculate # of days between dates, excluding holidays	abs2299	Excel Discussion (Misc queries)	8	March 3rd 05 02:21 AM

All times are GMT +1. The time now is 10:38 AM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement