I am working on a weekly paysheet and after I add the columns vertically will add either .25 hours or .5 hours. If the value of the sum is <= 3.50 add .25, if greater than 3.75 add .50. If the cell is blank or zero do not add anything. I was able to use the IF function for most of it but do not know how to specify that a blank or 0 cell should stay blank or 0.
Try this equation.
This would go in cell C1 if you are summing cells A1:B1
=IF(SUM(A1:B1)<=3.50,SUM(A1:B1)+.25,IF(SUM(A1:B1)>3.75,SUM(A1:B1)+.5,SUM(A1:B1)))
Regards,
amotto
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
It is all good except that I still have my 0 value cell add .25. I want the sum to be 0. Thanks,
or a slightly shorter version...
=if(SUM(A1:B1)=0,0,SUM(A1:B1)+IF(SUM(A1:B1)<=3.50,.25,IF(SUM(A1:B1)>3.75))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
oh okay, there are two ways to go about this, you can choose one:
option 1:
=IF(AND(SUM(A1:B1>0,SUM(A1:B1)<=3.50),SUM(A1:B1)+.25,IF(SUM(A1:B1)>3.75,SUM(A1:B1)+.5,SUM(A1:B1)))
Option 2:
=IF(IF(SUM(A1:B1)=0,0,SUM(A1:B1)<=3.50,SUM(A1:B1)+.25,IF(SUM(A1:B1)>3.75,SUM(A1:B1)+.5,SUM(A1:B1))))
These are assentially the same formula, the first is just saying anything under 0, not including blanks, is 0. The second is saying specifically if the value is 0 then put 0.
So, from 3.51 to 3.75 add nothing, correct?If the value of the sum is <= 3.50 add .25, if greater than 3.75 add .50.
If so, try this:
Assuming the sum range is A2:A10.
=IF(SUM(A2:A10),SUM(A2:A10)+LOOKUP(SUM(A2:A10),{0,3.51,3.76},{0.25,0,0.5}),"")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Your formula is incomplete.
I think fdibbins and myself were going off the assuption that Geogirl is only counting by .25 hours, but you are correct if you assume that you can count time in minutes.
Great Biff. Seems to work well. Thanks very much.
Thanks for your help.
Thanks for your help Amotto.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks