Autosum and formulas that result in text

[LAM]

I am using the Autosum command to sum a range of cells. The cells with
conditional formating similar to the formula below are not included in the
autosum. Can you tell me why?

=IF(C17>=12,"2.0",(IF(C17>=9,"1.5",IF(C17>5,"1",IF(C17>0,"0.5","0")))))

Thanks,
LAM

 

[Dave Peterson]

Your values are text--not numbers.

=IF(C17>=12,2,(IF(C17>=9,1.5,IF(C17>5,1,IF(C17>0,0.5,0)))))

 

[Shane Devenshire]

Hi,

1. You can't use that formula in Conditional Formatting for any useful
purpose.

2. Why store the numbers a text and then try to sum them?

3. If you must then change the autosum formua to something like this array:

=SUM(--A1:A20)

or this non-array

=SUMPRODUCT(--A1:A20)

Array - this means you need to press Shift+Ctrl+Enter to enter the formula.

 

[LAM]

Hi

Thanks for replying.

I am trying to assign office hours for instructors based on how long a class
is. Classes from 1-5 hours have 0.5 hours of office hours, classes >5 hours
have 1 office hour, etc. Then I want to sum the office hours in another
column.

I realized that I was generating text, but I wasn't sure if there was a way
to generate a value in a conditional formula. I guess not.

I am not familar with arrays, I will look into to them this morning.

thanks again.

 

[LAM]

THAT'S IT!!! I was sure that I tried that, but I guess not.

Thanks.