Scoring the cells

**ravisandhu** · 04-20-2009, 06:31 AM

hi there experts,

I hope you can help me with this problem that I have.

You'll see in the attachment that there are a range of behaviours D3 - D13.
The behaviours have a weighting, which is dependant on their level (found in C3 - C13.

When completing the form, the user will only enter data into the range E3-E13, where they will enter 0 for false and 1 for true

These are then added in ranges G3 to H13, and the sum is worked out at the bottom of each column

This is what I need to happen in the cells I14, J14, K14 and L14.

1. When F14, G14 and H14 all equal 0 (I14 should equal 1)

2. When F14 equals 3 and G14 equals smaller than 2 (J14 should equal 1)

3. When F14 equals 3 and G14 equals more than 2 and H14 equals less than 2 (K14 should equal 1)

4. When F14 equals 3 and G14 equals 4 and H14 equals more than 2 (L14 should equal 1)

I hope you can help me make the right formula, or something similar that will help me achieve a similar outcome.

Thanks in advanced.

Ravi

**martindwilson** · 04-20-2009, 06:48 AM

i14=IF(SUM(F14:H14)=0,1,"")
j14=IF(AND(F14=3,G14<2),1,"")

k14=IF(AND(F14=3,G14>2,H14<2),1,"")

l14=IF(AND(F14=3,G14=4,H14>2),1,"")

cant see what happens if g14=2 though

**dominicb** · 04-20-2009, 06:51 AM

Good morning ravisandhu

Are you aware that there is a gap between conditions 2 and 3? (ie if G14=2).
Also, what to do if the conditions aren't true? The formulae below will return nothing.

Originally Posted by ravisandhu

1. When F14, G14 and H14 all equal 0 (I14 should equal 1)

=IF(AND(F14=0,G14=0,H14=0)=TRUE,1,"")

Originally Posted by ravisandhu

2. When F14 equals 3 and G14 equals smaller than 2 (J14 should equal 1)

=IF(AND(F14=3,G14<2)=TRUE,1,"")

Originally Posted by ravisandhu

3. When F14 equals 3 and G14 equals more than 2 and H14 equals less than 2 (K14 should equal 1)

=IF(AND(F16=3,G16>2)=TRUE,1,"")

Originally Posted by ravisandhu

4. When F14 equals 3 and G14 equals 4 and H14 equals more than 2 (L14 should equal 1)

=IF(AND(F17=3,G17=4,H17>2)=TRUE,1,"")

HTH

DominicB

**ravisandhu** · 04-21-2009, 05:32 AM

hi Martin and Dominic,

Thanks so much for your response. Your suggestions have helped me move closer to what I am trying to achieve... but it's not where I want it to be yet... so i am hoping you can help me further.

I have attached an updated Excel sheet.

On the attachment, I have included a new cell M14.
M14 is the total rating that needs to be displayed, between 1 to 4.

if you try entering 1s in the E3-E13 range, as you work your way down, the ideal output is where the rating (M14) increases based on the criteria, i.e. if all the E3-E13 range are 1s, then the M14 rating should display 4.

I need the formulas in J14, K14 and L14 to work in such a way that when they have displayed a score from data in E3-E15 range, they do not become blank again.

I hope you have an idea (or ideas) of how to make this work.

I look forward to hearing from you.

Ravi

**ravisandhu** · 04-21-2009, 07:26 AM

I think I have an idea...

The formula in J14 is the only one that's not working for me, it is:

=IF(AND(F14=3,G14<2)=TRUE,1,"")

Is there a away of keeping this formula but including an OR IF statement, that if cell K14=1, then J14 should output 1 as well.

I hope this is possible, I just don't know how to do it.

I hope you can help.

many thanks.

Ravi