Formula

[Yitzhack]

Hi. This may be something simple but I’m not sure how to do it. I need a
formula where when value is greater than 5 but less than 8 = 0.5 and when is
greater than 0 but less than 5 = 1 at the same time all the data is
accumulating the points in cell A1. Thanks for the help

 

[Don Guillett]

=if(a1>5,.5,if(a1>0,1))
or
=if(a1>8,"",if(a1>5,.5,if(a1>0,1)))

 

[Yitzhack]

Thanks Don that’s exactly what I needed. Just one more question. How do I
expand that to multiple rows?
Thanks

--
Regards
YM

=if(a1>5,.5,if(a1>0,1))
or
=if(a1>8,"",if(a1>5,.5,if(a1>0,1)))

 

[Yitzhack]

I'm sorry, i meant columns

 

[Mike H]

Perhaps

=(COUNTIF($A$1:$A$10,">0")-COUNTIF($A$1:$A$10,">=5"))+((COUNTIF($A$1:$A$10,">=5")-COUNTIF($A$1:$A$10,">8"))/2)

Extend the ranges to suit

Mike

 

[Mike H]

It looks like you want to exclude 8 so try this instead

=(COUNTIF($A$1:$A$10,">0")-COUNTIF($A$1:$A$10,">=5"))+((COUNTIF($A$1:$A$10,">=5")-COUNTIF($A$1:$A$10,">=8"))/2)

Mike

 

[Mike H]

columns

=(COUNTIF(A1:K1,">0")-COUNTIF(A1:K1,">=5"))+((COUNTIF(A1:K1,">=5")-COUNTIF(A1:K1,">=8"))/2)

Mike

 

[Yitzhack]

Mike this works perfect but would you please tell me how to expanded to
multiple Columns as well.

Thanks again Mike

 

[Yitzhack]

Got it!!! THANKS Mike. and I'm sorry about the multiple posting. It wont
happen again.

 

[Mike H]

My other post is for a single row multiple column, this does multiple rows
and columns. Expand the range to suit.

=(COUNTIF(A1:K2,">0")-COUNTIF(A1:K2,">=5"))+((COUNTIF(A1:K2,">=5")-COUNTIF(A1:K2,">=8"))/2)