Last column used

A

art

I exported from QuickBooks a list frm cust. total of sales by the month. I
need in the last column a formula that should come up with the last month
purchased.


NAME Jan Feb Mar Apr Last month purchased
Abe 0 250 0 450 =Apr
Mark 500 0 0 0 =Jan
Larry 0 0 600 0 =Mar
Jack 0 350 0 0 =Feb


Thanks. ......."


__._,_.___
 
R

Ragdyer

Say your headers are in Row1, with Jan to Dec in B1 to M1,
And names down Column A, starting in A2.

Enter this *array* formula in N2:

=INDEX($B$1:$M$1,MAX(IF(B2:M2>0,COLUMN($A$1:$L$1))))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy the formula down Column N as needed.
 
R

Ragdyer

I also started out that way Don, but those 0's throw a monkey wrench into
the formula, since they *are* numbers.


Regards,

RD
 
T

Teethless mama

B1:E1 - hold months Jan to April
B2:E2 - hold values

In F2: =INDEX($B$1:$E$1,MAX(INDEX((B2:E2>0)*(COLUMN($A$1:$D$1)),0)))

Just hit ENTER
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top