Expected Min and Max Sales in Current Month

[RIZVI]

Hi,

In Excel 2007 I have customers name in Column A. From Column B to M, I enter the actual sales obtained from each customer against their name under respective month.

Some customers buy monthly. Some alternate month. Some once in three months and some once in a while.

In Column O I need to predict the MINIMUM current month's expected sales depending upon their buying pattern.
In Column P I need to predict the MAXIMUM current month's expected sales depending upon their buying pattern.


Suppose a customer who buys once in two months and has purchased last month, won't have any sales expected in current month. A customer who buys every month will have expected sales in current month. A customer who buys once in three months and has purchased two months back will have expected sales in current month otherwise not.

Is there some formula thru which this can be achieved?

Awaiting your response.

With regards,

Rizvi.M.H.

 

[S.H.A.D.O.]

Hi RIZVI,

I would suggest that you upload the file, will all sensitive information removed of course, so as to give us a better understanding of your data setup and requirements. It will make it easier visually.
It sounds like you want to use something like MEAN?

 

[Excelislife]

I tried this out, and if you highlight all the months for a company, you can then use the filling handle to stretch it across the next month. I tried it with different values, with different reorder months, and it will follow the trend. If it is only every 3 months, then it will only fill it in every 3 months. It will also try to do some sort of trend analysis based on your previous months. It is not an average of the previous numbers, so I'm not sure how it comes up with the numbers, but it is a good place holder.

As for the minimum and maximum, there is usually some criteria for determining that. For example, I might do:

=Average(B1:M1)+.2*Average(B1:M1)

For your maximum buying pattern. What is your criteria for determining Minimum and Maximum?

 

[RIZVI]

Hi,

I am trying to paste the date here:

[TABLE="width: 1198"]
<tbody>[TR]
[TD]Customers[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD][/TD]
[TD]Min Expected in Current Month[/TD]
[TD]Max Expected in Current Month[/TD]
[/TR]
[TR]
[TD]Perfect Engg[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]RO Solutions[/TD]
[TD="align: right"]3750[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]4250[/TD]
[TD="align: right"]2500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]420[/TD]
[/TR]
[TR]
[TD]RAM State[/TD]
[TD][/TD]
[TD="align: right"]1200[/TD]
[TD][/TD]
[TD="align: right"]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mel Enterp[/TD]
[TD="align: right"]4700[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]2750[/TD]
[TD="align: right"]6500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2750[/TD]
[TD="align: right"]6500[/TD]
[/TR]
</tbody>[/TABLE]

 

[S.H.A.D.O.]

I am trying to paste the date here:

Paste what date where?
In your initial post it doesn't mention copy and pasting?

 

[RIZVI]

Paste what date where?
In your initial post it doesn't mention copy and pasting?

Dear SHADO,

I have replied by pasting the information with sample data here as above. Hope that shall be sufficient to understand my requirement.

I have given months and customers name. Against each customer I have given some sales under different months. I have also given the min and max expected thus.

All this is done manually. I was loooking for a formula that will take the min & max value for current month from past purchase behaviour of customer.

Hope it is clear now.

RGDS,

Rizvi.M.H.