Formula Help to Calculate Quarterly Results


P

Paul Sheppard

I need a formula to calculate the quarterly sales of a product based on
the current month plus 2 previous months

Cell A3 indicates the current month formatted as mmm
Column A is the product starting at A5
Columns B - M are the sales by month, starting at B5 for Jan, C5 for
Feb etc

What is the formula I need in cell O5 to give me the quarterly sales
based on the value in cell A3

eg if the value in cell A3 is *Mar* the formula should add the sales in
cell B5:D5, if the value in A3 is changed to *Jun* the formula should
add the sales in cells E5:G5 etc

Thanks in advance for any help
 
Ad

Advertisements

P

Paul Sheppard

daddylonglegs said:
If A3 is a date formatted as mmm try

=SUM(OFFSET(B5:D5,,MAX(0,MONTH(A3)-3)))

Thanks daddylonglegs that worked a treat, have a similar thing in
another spreadsheet where the month in the cell is formatted as text eg
JAN, how would the above formula have to be changed to work in that
scenario?
 
D

daddylonglegs

In that case, assuming the same references as for your previous example
but with months, e.g. "Jan", "Feb" etc. in B4:M4

=SUM(OFFSET(B5:D5,,MAX(0,MATCH(A3,B4:M4,0)-3)))
 
Ad

Advertisements

P

Paul Sheppard

daddylonglegs said:
In that case, assuming the same references as for your previous example
but with months, e.g. "Jan", "Feb" etc. in B4:M4

=SUM(OFFSET(B5:D5,,MAX(0,MATCH(A3,B4:M4,0)-3)))

Thanks again
 

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