bobletcs123
New Member
- Joined
- Jan 31, 2019
- Messages
- 27
- Office Version
- 365
- Platform
- Windows
Hi All,
Have a sheet with labor hours. Previously, anything that was <1 would get rounded up to 1 and anything over would be actual number...
Then, we changed to just go with actual numbers all around. Easy enough, just change formula to have labor hours field times whatever the rate is (A2*78).
Now, we want to see about not rounding up to 1, but to the nearest quarter...
0.01-0.25 = 0.25 ; 0.26-0.50 = 0.50 ; 0.51-0.75 = 0.75 ; 0.75-1.00 = 1.00
So, getting error too many arguments. Must be missing another AND or OR? Basically, anything that says "Remote Diagnostics" has a $25 rate and everything else has $78 rate.
I think the main formula is good, but not when I add in the "Remote Diagnostic" secction.
Main: = IF(AND(J3>=0,J3<=0.25),0.25*78,IF(AND(J3>0.25,J3<=0.5),0.5*78,IF(AND(J3>0.5,J3<=0.75),0.75*78,IF(AND(J3>0.75,J3<=1),1*78,J3*78)))))
Adjusted: = IF(AND(G3="Remote Diagnostics",J3>=0,J3<=0.25),0.25*25,IF(AND(J3>0.25,J3<=0.5),0.5*25,IF(AND(J3>0.5,J3<=0.75),0.75*25,IF(AND(J3>0.75,J3<=1),1*25,J3*25,IF(AND(J3>=0,J3<=0.25),0.25*78,IF(AND(J3>0.25,J3<=0.5),0.5*78,IF(AND(J3>0.5,J3<=0.75),0.75*78,IF(AND(J3>0.75,J3<=1),1*78,J3*78)))))
Have a sheet with labor hours. Previously, anything that was <1 would get rounded up to 1 and anything over would be actual number...
Then, we changed to just go with actual numbers all around. Easy enough, just change formula to have labor hours field times whatever the rate is (A2*78).
Now, we want to see about not rounding up to 1, but to the nearest quarter...
0.01-0.25 = 0.25 ; 0.26-0.50 = 0.50 ; 0.51-0.75 = 0.75 ; 0.75-1.00 = 1.00
So, getting error too many arguments. Must be missing another AND or OR? Basically, anything that says "Remote Diagnostics" has a $25 rate and everything else has $78 rate.
I think the main formula is good, but not when I add in the "Remote Diagnostic" secction.
Main: = IF(AND(J3>=0,J3<=0.25),0.25*78,IF(AND(J3>0.25,J3<=0.5),0.5*78,IF(AND(J3>0.5,J3<=0.75),0.75*78,IF(AND(J3>0.75,J3<=1),1*78,J3*78)))))
Adjusted: = IF(AND(G3="Remote Diagnostics",J3>=0,J3<=0.25),0.25*25,IF(AND(J3>0.25,J3<=0.5),0.5*25,IF(AND(J3>0.5,J3<=0.75),0.75*25,IF(AND(J3>0.75,J3<=1),1*25,J3*25,IF(AND(J3>=0,J3<=0.25),0.25*78,IF(AND(J3>0.25,J3<=0.5),0.5*78,IF(AND(J3>0.5,J3<=0.75),0.75*78,IF(AND(J3>0.75,J3<=1),1*78,J3*78)))))