Formula Help!

[rob_kzk]

Hi All,

Hope someone can help with this...

I have 8 statements that can all have an answer between 0 and 4, if the statements total a value between two numbers this produces a pre-defined outcome, however what I have been asked to do is if all statements have an answer of 3 or 4 and only one of the answers is 2 then a different outcome is produced with the result being "3G".

I have re-created the sort of table I am looking at below with the results in columns B and E. If the outcome of all statements was as below this would produce the "3G" outcome but the spread of 3's and 4's and one 2 could be totally random.

A	B	C	D	E
Statement 1	3		Statement 5	4
Statement 2	4		Statement 6	4
Statement 3	2		Statement 7	3
Statement 4	3		Statement 8	4

<tbody>
</tbody>

What is the best formula to use for this?

Thank you for your help.

Rob

 

[oldbrewer]

I have 8 statements that can all have an answer between 0 and 4, if the statements total a value between two numbers this produces a pre-defined outcome, however what I have been asked to do is if all statements have an answer of 3 or 4 and only one of the answers is 2 then a different outcome is produced with the result being "3G".

I have re-created the sort of table I am looking at below with the results in columns B and E. If the outcome of all statements was as below this would produce the "3G" outcome but the spread of 3's and 4's and one 2 could be totally random.

Statement 1

3

row 6

Statement 2

2

0

abyssmal

Statement 3

2

6

terrible

Statement 4

3

12

bad

Statement 5

4

18

acceptable

Statement 6

4

24

good

Statement 7

3

28

excellent

Statement 8

4

good

32

superb

I made a lookup table with some pretend outcomes

the following formula produces either 3G or a word from the table

=IF(AND(COUNTIF($B$6:$B$13,3)+COUNTIF($B$6:$B$13,4)=7,COUNTIF($B$6:$B$13,2)=1),"3G",VLOOKUP(SUM($B$6:$B$13),mytable,2))

note score is 25 which is less than 28 so good is returned

<colgroup><col><col span="2"><col><col span="11"></colgroup><tbody>
</tbody>

 

[oldbrewer]

scroll across to see the lookup table

 

[rob_kzk]

That's great, thank you oldbrewer.

Just one further enhancement if I may, please can you confirm how I would need to update the formula if the table was set out in my original post with 4 statements producing answers in column B and 4 statements producing answers in column E? I have an inkling but if you could confirm it would be appreciated.

Thanks again.

Rob

 

[oldbrewer]

Statement 1	3		Statement 5	4
Statement 2	2		Statement 6	4							0	abyssmal
Statement 3	2		Statement 7	3							6	terrible
Statement 4	3		Statement 8	4							12	bad
											18	acceptable
											24	good
											28	excellent
											32	superb
good
note score is 25 which is less than 28 so good is returned
		new formula
	=IF(AND(COUNTIF($B$1:$B$4,3)+COUNTIF($E$1:$E$4,3)+COUNTIF($B$1:$B$4,4)+COUNTIF($E$1:$E$4,4)=7,COUNTIF($B$1:$B$4,2)+COUNTIF($E$1:$E$4,2)=1),"3G",VLOOKUP((SUM($B$1:$B$4)+SUM($E$1:$E$4)),mytable,2))

<colgroup><col><col span="2"><col><col span="20"></colgroup><tbody>
</tbody>