Autosum and formulas that result in text

L

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
 
D

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)))))
 
S

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.
 
L

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top