IF Function?

[Sarah]

I have a worksheet with values in cells A1&B1, D1&E1,
G1&H1. I would like to put a formula to obtain a total
which adds on 5 each time if A1>B1 and D1>E1 and G1>H1,
adds on 3 if A1=B1, D1=E1 and G1=H1 and adds zero for
anything else.

I have tried entering an IF formula in, it displays the
correct value e.g. 3 but does not add this onto the total
each time e.g. If A1>B1 and D1>E1 and G1>H1 I would expect
my total to be 9.

I hope this makes sense!

 

[Trevor Shuttleworth]

Sarah

=(IF(A1>B1,5,IF(A1=B1,3,0))+(IF(D1>E1,5,IF(D1=E1,3,0))+(IF(G1>H1,5,IF(G1=H1,
3,0)))))

Regards

Trevor

 

[JE McGimpsey]

one way:

= 5*((A1>B1)+(D1>E1)+(G1>H1)) + 3*((A1=B1)+(D1=E1)+(G1=H1))

 

[Sarah]

I have tried both these options and they calculate! - but
they also give me an answer when I do not have figures is
a cell, it seems that blank cells are treated the same as
cells that are equal to each other.

e.g. if A1 and B1 are blank, it classes them as A1=B1 and
puts the value 3 in the total

how can I get this to not calculate when a cell value is
blank?

 

[Frank Kabel]

Hi
try
= 5*((A1>B1)+(D1>E1)+(G1>H1)) +
3*((A1=B1)+(D1=E1)+(G1=H1))*(A1<>"")*(B1<>"")

 

[JE McGimpsey]

one way:

= (COUNT(A1:B1)=2)*(3*(A1=B1)+5*(A1>B1)) +
(COUNT(D1:E1)=2)*(3*(D1=E1)+5*(D1>E1)) +
(COUNT(G1:H1)=2)*(3*(G1=H1)+5*(G1>H1)) +

= 5*((A1>B1)+(D1>E1)+(G1>H1)) + 3*((A1=B1)*(COUNT(A1:B1)=2) +
(D1=E1)*(COUNT(D1:E1)=2) + (G1=H1)*(COUNT(G1:H1)=2))

 

[Sarah]

That worked! - Thanks very much!

-----Original Message-----
one way:

= (COUNT(A1:B1)=2)*(3*(A1=B1)+5*(A1>B1)) +
(COUNT(D1:E1)=2)*(3*(D1=E1)+5*(D1>E1)) +
(COUNT(G1:H1)=2)*(3*(G1=H1)+5*(G1>H1)) +

= 5*((A1>B1)+(D1>E1)+(G1>H1)) + 3*((A1=B1)*(COUNT (A1:B1)=2) +
(D1=E1)*(COUNT(D1:E1)=2) + (G1=H1)*(COUNT(G1:H1)=2))


.