Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.