conditional formula needed

[ruthm8611]

I have the following data and need a formula that will return a number depending on the outcome for the criteria.
Points
Goal1 & Goal2 >= 125% and Goal3 >= 100% 5
Goal1 >= 125% and Goal2 and Goal 3 >=100% 4
Goal 1, 2, and 3 >= 100% 3
Goal 1 and/or 2 and/or 3 >= 100% 2


Thank you in advance for any assitance.

 

[Akashwani]

Hi,

You don't say what Goal1, 2, 3 are, I guess they will be cell references.
So based on that assumption try this....

Excel Workbook
	A	B	C	D	E	F
2	Goal1	Goal2	Goal3	Points	*	Criteria
3	100%	101%	125%	3	*	100%
4	*	*	*	*	*	125%
Sheet3

I'm not sure if this is the best solution for you.

Good Luck

Ak

 

[ruthm8611]

Thank you for the time you spent on this. The only part that doesn't work is the OR string. I need this part to return a 2 if at least two of the three goals are 100%. Any help is greatly appreciated.

 

[Akashwani]

Hi,

I'm confused, which probably isn't your fault.
If you want to show 2 when any of the 2 criteria are >= 100% then this will do it if the 3rd criteria is less than 100%....

=IF(AND(A3>=F4,B3>=F4,C3>=F3),5,IF(AND(A3>=F4,B3>=F3,C3>=F3),4,IF(AND(A3>=F3,B3>=F3,C3>=F3),3,IF(OR(AND(A3>=F3,B3>=F3),AND(B3>=F3,C3>=F3),AND(A3>=F3,C3>=F3)),2,"Oops"))))

I've probably totally misunderstood how you require the results to be constructed, sorry. This formula looks long and I guess there will be someone with a shorter smarter solution for you.

Ak