Question on an IF statement

[jjackson]

Im trying to get this if statement to count up to 40 and post the results up to 40 in the designated cell, in this case its I18. The problem I am having is that when it adds up the cells from f, g, and h, it takes the whole number in the cell and removes it if the total is over 40. However what I need it to do is split up the number until that cell equals exactly 40, and then have the remainder go to a different cell with opposite formula.


=IF((AND(I18>0,SUM(F17:F21)+SUM(G17:G21)+SUM(H17:H21)+SUM(I17:I18)<40)),I18,0)

Thanks for your help.

 

[nbrcrunch]

to make life easier, name the ranges you are summing. I'll call it AllMyCells

Then surround your statement with a test for result = INT(result)

=if(result<>int(result),[the rest of your statement],int([the rest of your statement]))

then in another cell use
=if(result<>int(result),mod(the rest of your statement),"")

 

[jjackson]

Im not sure that I can name the ranges becouse I would wind up with about 40 different ranges. I will try it and see how it works.
(I tried to name the ranges but it wouldnt let me name a range over 2 columns.)
could you possibly show me what the completed formula would look like, I dont really understand everything that you posted. Sorry...
Thanks for the help

 

[nbrcrunch]

Your original formula indicated only three ranges. In the above post you now clarify that to be more than 40 ranges. So without correct details it is impossible for me to write the formula out. However, let me try to put my concept into plain English and see if that will help you.

In case you don't know, INT() takes just the integer (whole number) of a value. MOD() takes only the remainder.

As I understood you original post, you wanted to have the integer part of the SUM go into i18 and the remainder into another part. If you surround your current IF statement with another one that tests for integer and remainder, you can have the formulas do just as you indicated.

 

[jjackson]

It might be easier for me to send you a copy of the sheet so you can see exactly what is going on.
Its for a time sheet and I have to add multiple cells. Starting with one cell and then each step I have to add the next cell in the line. Currently it is 35 cells total becouse I have 5 steps in each day, over a 7 day period. I have to add the times in each cell until I reach 40 at which point they have to go to another section of the time sheet and are calculated there. The way I have it set up now is that the time gets entered into 35 cells then right below the 35 cells figure up to 40 hours, then below that the next 35 cells figure everything over 40 hours. The set up is 5 rows and 7 columns per. Let me try to give you an example. When adding the cells lets say I have a total of 38 hours in the 40 hour table, if I enter 5 into the next cell it will take the whole 5 and move down to the overtime cells. Which in turn gives me to many ot hours and not enough regular hours.
Sorry this is rather confusing but I am not sure how else to explain it without sending a page to you.
Thanks for your help on this.

 

[nbrcrunch]

perhaps someone else will be willing to accept that offer.

 

[jjackson]

Book1
	A	B	C	D
1	10	7
2	10	5
3	10
4
5
6	=IF((AND(A1>0,A1<=40)),A1,0)	=IF((AND(B1>0,SUM(A1:A5)+SUM(B1)<=40)),B1,0)	=IF((AND(C1>0,SUM(A1:A5)+SUM(B1:B5)+SUM(C1)<=40)),C1,0)
7	=IF((AND(A2>0,SUM(A1:A2)<=40)),A2,0)	=IF((AND(B2>0,SUM(A1:A5)+SUM(B1:B2)<=40)),B2,0)	=IF((AND(C2>0,SUM(A1:A5)+SUM(B1:B5)+SUM(C1:C2)<=40)),C2,0)
8	=IF((AND(A3>0,SUM(A1:A3)<=40)),A3,0)	=IF((AND(B3>0,SUM(A1:A5)+SUM(B1:B3)<=40)),B3,0)	=IF((AND(C3>0,SUM(A1:A5)+SUM(B1:B5)+SUM(C1:C3)<=40)),C3,0)
9	=IF((AND(A4>0,SUM(A1:A4)<=40)),A4,0)	=IF((AND(B4>0,SUM(A1:A5)+SUM(B1:B5)<=40)),B4,0)	=IF((AND(C4>0,SUM(A1:A5)+SUM(B1:B5)+SUM(C1:C5)<=40)),C4,0)
10	=IF((AND(A5>0,SUM(A1:A5)<=40)),A5,0)	=IF((AND(B5>0,SUM(A1:A5)+SUM(B1:B5)<=40)),B5,0)	=IF((AND(C5>0,SUM(A1:A5)+SUM(B1:B5)+SUM(C1:C5)<=40)),C5,0)
11	=IF((AND(A1>0,A1>40)),A1,0)	=IF((AND(B1>0,SUM(A1:A5)+SUM(B1)>40)),B1,0)	=IF((AND(C1>0,SUM(A1:A5)+SUM(B1:B5)+SUM(C1)>40)),C1,0)
12	=IF((AND(A2>0,SUM(A1:A2)>40)),A2,0)	=IF((AND(B2>0,SUM(A1:A5)+SUM(B1:B2)>40)),B2,0)	=IF((AND(C2>0,SUM(A1:A5)+SUM(B1:B5)+SUM(C1:C2)>40)),C2,0)
13	=IF((AND(A3>0,SUM(A1:A3)>40)),A3,0)	=IF((AND(B3>0,SUM(A1:A5)+SUM(B1:B3)>40)),B3,0)	=IF((AND(C3>0,SUM(A1:A5)+SUM(B1:B5)+SUM(C1:C3)>40)),C3,0)
14	=IF((AND(A4>0,SUM(A1:A4)>40)),A4,0)	=IF((AND(B4>0,SUM(A1:A5)+SUM(B1:B4)>40)),B4,0)	=IF((AND(C4>0,SUM(A1:A5)+SUM(B1:B5)+SUM(C1:C4)>40)),C4,0)
15	=IF((AND(A5>0,SUM(A1:A5)>40)),A5,0)	=IF((AND(B5>0,SUM(A1:A5)+SUM(B1:B5)>40)),B5,0)	=IF((AND(C5>0,SUM(A1:A5)+SUM(B1:B5)+SUM(C1:C5)>40)),C5,0)
16
17
18
19	=SUM(A6:A10)	=SUM(B6:B10)	=SUM(C6:C10)
20	=SUM(A11:A15)	=SUM(B11:B15)	=SUM(C11:C15)
Sheet1

 

[jjackson]

What I am trying to get it to do is take the 5 from b2 and split it so that 3 of it goes to cell b7 and 2 will go to cell b12.
This is only a portion of the worksheet for space reasons I could only show the first three columns with the formula's.

 

[jjackson]

TOPPS TIME.xls
	D	E	F	G	H	I
15	STEP	ACTIVITY	SUN	MON	TUE	WED
16	NUMBER	CODE	12/4	12/5	12/6	12/7
17	103	SUPER	10.00	7.00
18	503	SUPER	10.00	5.00
19	504	SUPER	10.00
20	505	SUPER
21	130	SUPER
22	S103	SUPER	10.00	7.00	-	-
23	S503	SUPER	10.00	-	-	-
24	S504	SUPER	10.00	-	-	-
25	S505	SUPER	-	-	-	-
26	S130	SUPER	-	-	-	-
27	S103OT	SUPER	-	-	-	-
28	S503OT	SUPER	-	5.00	-	-
29	S504OT	SUPER	-	-	-	-
30	S505OT	SUPER	-	-	-	-
31	S130OT	SUPER	-	-	-	-
32
33
34
35			30.00	7.00	-	-
36			-	5.00	-	-
ONE