Question on an IF statement

jjackson

New Member
Joined
Nov 21, 2005
Messages
28
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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

New Member
Joined
Nov 21, 2005
Messages
28
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

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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

New Member
Joined
Nov 21, 2005
Messages
28

ADVERTISEMENT

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.
 

jjackson

New Member
Joined
Nov 21, 2005
Messages
28

ADVERTISEMENT

 

jjackson

New Member
Joined
Nov 21, 2005
Messages
28
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,056
Messages
5,569,953
Members
412,299
Latest member
agentless
Top