Thread: One formula for all these ifs, how can I do it?

Hi there,

The users will be recording into the excel sheet, under column 'Input criteria' either a 0(i.e.false) or 1(i.e.true)

There are competency statements [D3:15] which are within a 'level' [C3:15] (these levels need to influence the formula)

I need to create a formula in cells G4 and G15, which results with one of the following score, either 1, 2, 3 or 4.

The results need to be worked out with the following criteria:

If "The range E3:E15 = 0" [score = 1]

If: C3:15 = 2 AND E3:15 = 1 &
where C3:15 = 3 AND >50% of E3:15 = 1 [score = 2]

If: C3:15 = 2 AND E3:15 = 1 &
Where C3:15 = 3 AND >50% of E3:15 = 1 &
Where C3:15 = 4 AND >50% of E3:15 = 1 [Score = 3]

If C3:15 = 2 AND E3:15 = 1 &
where C3:15 = 3 AND E3:15 = 1 &
Where C3:15 = 4 AND if >50% of E3:15 = 1 [Score = 4]

I need one formula that could be placed in either G4 and G15 that would work for all the above criteria.

How would I go about doing it?

I hope someone can help me, many thanks in advanced.

Ravi

Are you talking about SUMS?

=IF(SUM(E3:E15)=0,1,moretocome....

And for 2...more sums? If I SUM C4:C15 the answer will be far more than 4.
Do you mean averages? AVERAGE(C4:C14)?

You original question and sheet really don't fully explain what you mean when evaluating the ranges C and E.

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!