Home |
Search |
Today's Posts |
#1
|
|||
|
|||
INT and MOD
Hello, one and all.
I have two cell formulas as follows. =INT($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7) =MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7,1)*($S$4/5) The first gives me the number of whole days holiday available. The second gives me the number of hours left (up to 8) (S4 can be either 37.50 or 40) Please can you tell me why, that if the number of holidays left was was 1/2 a day,(0.50) does the result give -1 for the first formula and 4 for the second formula. Surely the INT of 0.50 should be 0. Your help is and always has been greatly appreciated. Thanking you in anticipation. -- Big Rick |
#2
|
|||
|
|||
Give us some sample data otherwise it takes a lot of effort to create data
that fits that circumstance. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello, one and all. I have two cell formulas as follows. =INT($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7) =MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7,1)*($S$4/5) The first gives me the number of whole days holiday available. The second gives me the number of hours left (up to 8) (S4 can be either 37.50 or 40) Please can you tell me why, that if the number of holidays left was was 1/2 a day,(0.50) does the result give -1 for the first formula and 4 for the second formula. Surely the INT of 0.50 should be 0. Your help is and always has been greatly appreciated. Thanking you in anticipation. -- Big Rick |
#3
|
|||
|
|||
Hello.
Before I start, I'm afraid I made a slight mistake in my explanation. All cell references refer to holiday entitlement, holidays taken, additional hours worked. etc. (as each of the cells references are the results of formulas themselves, I'm sure it would sound double dutch if I tried explaining each one.) The problem and were I made my mistake is that if I used all my holidays and both cells equalled zero and then I took half a days holiday, I would owe 4 hours. The result of each formula is -1 for the first. 4 for the second. I would like the result to be 0 for the first. -4 for the second. Hoping you can understand this now. -- Big Rick "Bob Phillips" wrote: Give us some sample data otherwise it takes a lot of effort to create data that fits that circumstance. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello, one and all. I have two cell formulas as follows. =INT($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7) =MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7,1)*($S$4/5) The first gives me the number of whole days holiday available. The second gives me the number of hours left (up to 8) (S4 can be either 37.50 or 40) Please can you tell me why, that if the number of holidays left was was 1/2 a day,(0.50) does the result give -1 for the first formula and 4 for the second formula. Surely the INT of 0.50 should be 0. Your help is and always has been greatly appreciated. Thanking you in anticipation. -- Big Rick |
#4
|
|||
|
|||
It would still be easier if you gave us an example of the data in A1, I6,
and all the rest where you get the problem. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello. Before I start, I'm afraid I made a slight mistake in my explanation. All cell references refer to holiday entitlement, holidays taken, additional hours worked. etc. (as each of the cells references are the results of formulas themselves, I'm sure it would sound double dutch if I tried explaining each one.) The problem and were I made my mistake is that if I used all my holidays and both cells equalled zero and then I took half a days holiday, I would owe 4 hours. The result of each formula is -1 for the first. 4 for the second. I would like the result to be 0 for the first. -4 for the second. Hoping you can understand this now. -- Big Rick "Bob Phillips" wrote: Give us some sample data otherwise it takes a lot of effort to create data that fits that circumstance. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello, one and all. I have two cell formulas as follows. =INT($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7) =MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7,1)*($S$4/5) The first gives me the number of whole days holiday available. The second gives me the number of hours left (up to 8) (S4 can be either 37.50 or 40) Please can you tell me why, that if the number of holidays left was was 1/2 a day,(0.50) does the result give -1 for the first formula and 4 for the second formula. Surely the INT of 0.50 should be 0. Your help is and always has been greatly appreciated. Thanking you in anticipation. -- Big Rick |
#5
|
|||
|
|||
Hi Rick
One way Wrap your first formula in a MAX() function =MAX(0,yourformula) and lets say that formula is in A1 Then in the second formula =(yourformula)*(-1*A1<1) Regards Roger Govier Big Rick wrote: Hello. Before I start, I'm afraid I made a slight mistake in my explanation. All cell references refer to holiday entitlement, holidays taken, additional hours worked. etc. (as each of the cells references are the results of formulas themselves, I'm sure it would sound double dutch if I tried explaining each one.) The problem and were I made my mistake is that if I used all my holidays and both cells equalled zero and then I took half a days holiday, I would owe 4 hours. The result of each formula is -1 for the first. 4 for the second. I would like the result to be 0 for the first. -4 for the second. Hoping you can understand this now. |
#6
|
|||
|
|||
example. I6=20 A1=3 Z6=0 A13=0 A17=20 A21=0 Y7=3 S4=40
These are example figures. The result of these formulas with the above values should be 0 for both the INT and MOD. IF A17 = 20.50 Then I want the first formula to = 0 (currently -1) and the second formula to = -4 (currently 4) I know you are the man for this job, as it was you who help me with these formulas in the first place! Many thanks for your time and patience of putting up with me. -- Big Rick "Bob Phillips" wrote: It would still be easier if you gave us an example of the data in A1, I6, and all the rest where you get the problem. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello. Before I start, I'm afraid I made a slight mistake in my explanation. All cell references refer to holiday entitlement, holidays taken, additional hours worked. etc. (as each of the cells references are the results of formulas themselves, I'm sure it would sound double dutch if I tried explaining each one.) The problem and were I made my mistake is that if I used all my holidays and both cells equalled zero and then I took half a days holiday, I would owe 4 hours. The result of each formula is -1 for the first. 4 for the second. I would like the result to be 0 for the first. -4 for the second. Hoping you can understand this now. -- Big Rick "Bob Phillips" wrote: Give us some sample data otherwise it takes a lot of effort to create data that fits that circumstance. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello, one and all. I have two cell formulas as follows. =INT($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7) =MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7,1)*($S$4/5) The first gives me the number of whole days holiday available. The second gives me the number of hours left (up to 8) (S4 can be either 37.50 or 40) Please can you tell me why, that if the number of holidays left was was 1/2 a day,(0.50) does the result give -1 for the first formula and 4 for the second formula. Surely the INT of 0.50 should be 0. Your help is and always has been greatly appreciated. Thanking you in anticipation. -- Big Rick |
#7
|
|||
|
|||
INT is effectively rounding down, so that is why a value of -0.5 goes to -1.
Do you want all negative values to go to 0, or to round ? If just round up then use =IF($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7<0,ROUNDDOWN($I$6+A1+$Z$6+$A$13-$A$17 -$A$21-$Y$7,0),INT(($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7))) And by what criteria should the second formula result in -4? If it is just that the sum value is negative, then use =SIGN($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7)*MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$2 1-$Y$7,1)*($S$4/5) -- HTH Bob Phillips "Big Rick" wrote in message ... example. I6=20 A1=3 Z6=0 A13=0 A17=20 A21=0 Y7=3 S4=40 These are example figures. The result of these formulas with the above values should be 0 for both the INT and MOD. IF A17 = 20.50 Then I want the first formula to = 0 (currently -1) and the second formula to = -4 (currently 4) I know you are the man for this job, as it was you who help me with these formulas in the first place! Many thanks for your time and patience of putting up with me. -- Big Rick "Bob Phillips" wrote: It would still be easier if you gave us an example of the data in A1, I6, and all the rest where you get the problem. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello. Before I start, I'm afraid I made a slight mistake in my explanation. All cell references refer to holiday entitlement, holidays taken, additional hours worked. etc. (as each of the cells references are the results of formulas themselves, I'm sure it would sound double dutch if I tried explaining each one.) The problem and were I made my mistake is that if I used all my holidays and both cells equalled zero and then I took half a days holiday, I would owe 4 hours. The result of each formula is -1 for the first. 4 for the second. I would like the result to be 0 for the first. -4 for the second. Hoping you can understand this now. -- Big Rick "Bob Phillips" wrote: Give us some sample data otherwise it takes a lot of effort to create data that fits that circumstance. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello, one and all. I have two cell formulas as follows. =INT($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7) =MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7,1)*($S$4/5) The first gives me the number of whole days holiday available. The second gives me the number of hours left (up to 8) (S4 can be either 37.50 or 40) Please can you tell me why, that if the number of holidays left was was 1/2 a day,(0.50) does the result give -1 for the first formula and 4 for the second formula. Surely the INT of 0.50 should be 0. Your help is and always has been greatly appreciated. Thanking you in anticipation. -- Big Rick |
#8
|
|||
|
|||
I am signing off for the night, so I will give these a go when I get home. I
will post back tomorrow if I have any further problems. Thanks for your time. -- Big Rick "Bob Phillips" wrote: INT is effectively rounding down, so that is why a value of -0.5 goes to -1. Do you want all negative values to go to 0, or to round ? If just round up then use =IF($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7<0,ROUNDDOWN($I$6+A1+$Z$6+$A$13-$A$17 -$A$21-$Y$7,0),INT(($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7))) And by what criteria should the second formula result in -4? If it is just that the sum value is negative, then use =SIGN($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7)*MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$2 1-$Y$7,1)*($S$4/5) -- HTH Bob Phillips "Big Rick" wrote in message ... example. I6=20 A1=3 Z6=0 A13=0 A17=20 A21=0 Y7=3 S4=40 These are example figures. The result of these formulas with the above values should be 0 for both the INT and MOD. IF A17 = 20.50 Then I want the first formula to = 0 (currently -1) and the second formula to = -4 (currently 4) I know you are the man for this job, as it was you who help me with these formulas in the first place! Many thanks for your time and patience of putting up with me. -- Big Rick "Bob Phillips" wrote: It would still be easier if you gave us an example of the data in A1, I6, and all the rest where you get the problem. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello. Before I start, I'm afraid I made a slight mistake in my explanation. All cell references refer to holiday entitlement, holidays taken, additional hours worked. etc. (as each of the cells references are the results of formulas themselves, I'm sure it would sound double dutch if I tried explaining each one.) The problem and were I made my mistake is that if I used all my holidays and both cells equalled zero and then I took half a days holiday, I would owe 4 hours. The result of each formula is -1 for the first. 4 for the second. I would like the result to be 0 for the first. -4 for the second. Hoping you can understand this now. -- Big Rick "Bob Phillips" wrote: Give us some sample data otherwise it takes a lot of effort to create data that fits that circumstance. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello, one and all. I have two cell formulas as follows. =INT($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7) =MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7,1)*($S$4/5) The first gives me the number of whole days holiday available. The second gives me the number of hours left (up to 8) (S4 can be either 37.50 or 40) Please can you tell me why, that if the number of holidays left was was 1/2 a day,(0.50) does the result give -1 for the first formula and 4 for the second formula. Surely the INT of 0.50 should be 0. Your help is and always has been greatly appreciated. Thanking you in anticipation. -- Big Rick |
#9
|
|||
|
|||
No time to try tonight but will try when I get home.
Will post back if I ahve any further problems. Thanks for your time. -- Big Rick "Roger Govier" wrote: Hi Rick One way Wrap your first formula in a MAX() function =MAX(0,yourformula) and lets say that formula is in A1 Then in the second formula =(yourformula)*(-1*A1<1) Regards Roger Govier Big Rick wrote: Hello. Before I start, I'm afraid I made a slight mistake in my explanation. All cell references refer to holiday entitlement, holidays taken, additional hours worked. etc. (as each of the cells references are the results of formulas themselves, I'm sure it would sound double dutch if I tried explaining each one.) The problem and were I made my mistake is that if I used all my holidays and both cells equalled zero and then I took half a days holiday, I would owe 4 hours. The result of each formula is -1 for the first. 4 for the second. I would like the result to be 0 for the first. -4 for the second. Hoping you can understand this now. |
#10
|
|||
|
|||
I would like to nominate you for President.
This works exactly how I wanted. Thank you so much. -- Big Rick "Bob Phillips" wrote: INT is effectively rounding down, so that is why a value of -0.5 goes to -1. Do you want all negative values to go to 0, or to round ? If just round up then use =IF($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7<0,ROUNDDOWN($I$6+A1+$Z$6+$A$13-$A$17 -$A$21-$Y$7,0),INT(($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7))) And by what criteria should the second formula result in -4? If it is just that the sum value is negative, then use =SIGN($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7)*MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$2 1-$Y$7,1)*($S$4/5) -- HTH Bob Phillips "Big Rick" wrote in message ... example. I6=20 A1=3 Z6=0 A13=0 A17=20 A21=0 Y7=3 S4=40 These are example figures. The result of these formulas with the above values should be 0 for both the INT and MOD. IF A17 = 20.50 Then I want the first formula to = 0 (currently -1) and the second formula to = -4 (currently 4) I know you are the man for this job, as it was you who help me with these formulas in the first place! Many thanks for your time and patience of putting up with me. -- Big Rick "Bob Phillips" wrote: It would still be easier if you gave us an example of the data in A1, I6, and all the rest where you get the problem. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello. Before I start, I'm afraid I made a slight mistake in my explanation. All cell references refer to holiday entitlement, holidays taken, additional hours worked. etc. (as each of the cells references are the results of formulas themselves, I'm sure it would sound double dutch if I tried explaining each one.) The problem and were I made my mistake is that if I used all my holidays and both cells equalled zero and then I took half a days holiday, I would owe 4 hours. The result of each formula is -1 for the first. 4 for the second. I would like the result to be 0 for the first. -4 for the second. Hoping you can understand this now. -- Big Rick "Bob Phillips" wrote: Give us some sample data otherwise it takes a lot of effort to create data that fits that circumstance. -- HTH Bob Phillips "Big Rick" wrote in message ... Hello, one and all. I have two cell formulas as follows. =INT($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7) =MOD($I$6+A1+$Z$6+$A$13-$A$17-$A$21-$Y$7,1)*($S$4/5) The first gives me the number of whole days holiday available. The second gives me the number of hours left (up to 8) (S4 can be either 37.50 or 40) Please can you tell me why, that if the number of holidays left was was 1/2 a day,(0.50) does the result give -1 for the first formula and 4 for the second formula. Surely the INT of 0.50 should be 0. Your help is and always has been greatly appreciated. Thanking you in anticipation. -- Big Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|