Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a number of columns indicating sales per month for example the following might indicate that two months ago there was a sale from this customer of $3555.

0,0,0,0,0,0,0,0,0,3555,0,0

I need a formula to calculate the number of months since the last sale, i.e. the result in this case should be 2.

share|improve this question

Here's an quick solution for this. However, you need a helper row, which means, there is room for improvement. Maybe someone else can take it from here.

This is the setup:

month 1 2 3 4 5 6 7 8 9 10 11 12
sale  0 0 0 0 5 0 0 0 8 11 0  0 
help  0 0 0 0 5 0 0 0 9 10 0  0

help-formula:

IF(SALE>0,MONTH,0) - IF(A2>0,A1,0)

months since last sale:

MAX(A1:L1)-MATCH(MAX(A3:L3);A3:L3;0)

The main problem is to get to the max column number with a sale value greater than 0. Right now, I can only think of VBA & Matrix-Function to solve this without the helper-row. But I have the feeling I am missing something.

share|improve this answer
    
Thanks for the idea in the last paragraph. – Jüri Ruut Nov 10 '12 at 21:04

Elaborating further Jook's answer:

Results for months 1 to 12 are located in columns A1:L1

We can use an array formula to find the desired result (Ctrl-Shift-Enter from a formula window to insert, curled brackets are inserted by Excel itself):

={COLUMNS(A1:L1)-LARGE(IF(A1:L1>0,COLUMN(A1:L1),"0"),1)}

We store the column numbers for the cells where sales are greater than 0 and find the greatest column number (i.e. closest to the end of the reporting period). Then we need to distract the value from the overall number of columns.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .