Need help with creating a complex formula -

[crazy_papaya]

Hello all,

I have started to give up and can use some help from the experts. This is just the first part of the problem. So here is what I am trying to achieve -

VENDOR NAME	PRODUCT	1	2	3	4	5	6	7	8	9	10	11	12	FREQUENCY (f)	FORECAST
Vendor1	P1	24	18	36	35	30	79	52	43	37	30	79	31	12	?

First I calculated the frequency of the times that we had an entry which was >= 1 in Months (1-12). I used COUNTIF for that and it worked fine. Now, I have a three part criteria that needs to be set in order to create a forecast for the item. Below is the three part criteria -

If f<=1 then forecast = 0
If f>=2 and f<6 then forecast formula is MAX number out of 12 months
If f>=6 then forecast formula is AVERAGE is average of 12 months

I am just wondering if it will be possible to put all of the criterias into one column. Any thoughts or suggestions?

 

[AhoyNC]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	VENDOR NAME	PRODUCT	1	2	3	4	5	6	7	8	9	10	11	12	FREQUENCY (f)	FORECAST
2	Vendor1	P1	24	18	36	35	30	79	52	43	37	30	79	31	12	41.167
Sheet1

Cell Formulas
Range	Formula
P2	P2	=IF(O2<=1,0,IF(O2<6,MAX(C2:N2),AVERAGE(C2:N2)))

 

[crazy_papaya]

Oh wow! works like magic. Thank you AhoyNC

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[crazy_papaya]

Hi AhoyNC,

I got a chance to work carefully with the formula you help create and just want to say thank you, you really simplified it. I feel stupid but I am very grateful to you

 

[AhoyNC]

You're very welcome. No need to feel stupid, Excel can be very frustrating at times.