Nested IF statement problem with < and >

Hi all,

A really stupid question that is probably easy has been bugging me for nearly an hour.

I have a pivot table that gives the totals in hours for student's work in a given period. There are some students that have done no work and some who have done 20 minutes. There are also some who have done 18 or even 35 hours

My pivot column shows figures such as this sample with the data starting in cell C5 and formatted as number so 1 hr 30 minutes is returned as 1.5:
0.28
0.00
18.00
0.00
0.00
0.00
0.00
0.00
38.00
0.00
17.45
6.28

I have decided to give points as follows:
If a student has done no work then the column should show 0
If a student has done over .05 hours they get 1 point per 0.5 hrs

My IF statement is working so far.

However if they have done more than 0 but less than 0.5 I still want to give them 1 point so for example the first student on my column above has done 0.28hrs so he/she will get 1 point. This is where my formula breaks down and where I need help!

Thanks in advance.

Simon

Try this formula:

=IF(B1>0,IF(B1>0.5,ROUNDUP(B1/0.5*1,0),1),0)

where B1, is the first value (in your example it would be 0.28).

Thanks, but for some reason the results are doubled so 0.28 is returning 1 and 17.45 is returning 35 instead of 0.5 and 18.

"However if they have done more than 0 but less than 0.5 I still want to give them 1 point so for example the first student on my column above has done 0.28hrs so he/she will get 1 point."

-- Isn't that what you requested?

DOH!

Yes, exactly what I asked for thanks.

However it would be great if you could let me know how I could do the same thing but return the number of hours which is what I thought I would see. So, 0.28 would return 0.5, 18.00 would return 18.00 and 0 would return 0.

Your answer was perfect but I am trying to understand exactly how the formula works. If I could see them both to compare, that would be fantastic!

You could also write the formula in separate parts as follows (might make more sense):

=IF(B1>0, C1, 0)
Pretty straight forward. If B1 is greater than zero the value is assigned by cell C1. Otherwise the value is simply zero.

Cell C1: IF(B1>0.5, B1/0.5*1) -- this part looks at the value in B1, calculates the number of half hour intervals and multiplies 1 pt for each interval)

BUT, you have fractions of 0.5 hr intervals to deal with, and and fraction less than 0.5 hrs but greater than 0.05 hrs is equal to 1 point. So, 17.45 hrs is (17/0.5 + 1) points. OR, you could just round up your calculation to an interger as such ---
Cell C1: IF(B1>0.5, ROUNDUP(B1/0.5*1,0),1)
In this case if B1 is less than 0.5 it will assign the value simply as 1 (I didn't include a test to verify if the value was greater than 0.05 hrs, or 3 minutes).

When in doubt, break the problem into smaller, individual calculations. Then, reference those cells for use in the larger formula. For larger complex calculatios, I always do this until I can verify the logic is working. Easier to check as well.

Thanks, very clear and concise.

Another thing completely understood. Breaking it down makes sense. Thanks again