| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to round down the result of a formula
i am trying to get excel to round down (instead of round up) the result of
the following formula: =IF((100-(((I2)-0.75)*144))100,100,(100-(((I2)-0.75)*144))). the input in I2 is 21:24, which is in the [h]:mm format. The answer is 79.6, but excel will by default display 80. I have noted the =ROUNDDOWN function, but i do not know if it can be applied within another formula. If so, then how? If not, then how can i get the result to round down? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to round down the result of a formula
One way (to round down to the nearest integer):
=MIN(100,INT(100-(I2-0.75)*144)) In article , Desdinova wrote: i am trying to get excel to round down (instead of round up) the result of the following formula: =IF((100-(((I2)-0.75)*144))100,100,(100-(((I2)-0.75)*144))). the input in I2 is 21:24, which is in the [h]:mm format. The answer is 79.6, but excel will by default display 80. I have noted the =ROUNDDOWN function, but i do not know if it can be applied within another formula. If so, then how? If not, then how can i get the result to round down? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to round down the result of a formula
this was very helpful. thank you!
"JE McGimpsey" wrote: One way (to round down to the nearest integer): =MIN(100,INT(100-(I2-0.75)*144)) In article , Desdinova wrote: i am trying to get excel to round down (instead of round up) the result of the following formula: =IF((100-(((I2)-0.75)*144))100,100,(100-(((I2)-0.75)*144))). the input in I2 is 21:24, which is in the [h]:mm format. The answer is 79.6, but excel will by default display 80. I have noted the =ROUNDDOWN function, but i do not know if it can be applied within another formula. If so, then how? If not, then how can i get the result to round down? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to add conditions to an if statement
ok, for some reason i cannot write a new post (maybe i can only have one?).
So i will ask it he how can i add to an "if" function. As in, i want to say =IF M626 AND T6<135, "1st class" can i do this, and effectively add two conditions for one if statement? "Desdinova" wrote: i am trying to get excel to round down (instead of round up) the result of the following formula: =IF((100-(((I2)-0.75)*144))100,100,(100-(((I2)-0.75)*144))). the input in I2 is 21:24, which is in the [h]:mm format. The answer is 79.6, but excel will by default display 80. I have noted the =ROUNDDOWN function, but i do not know if it can be applied within another formula. If so, then how? If not, then how can i get the result to round down? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to round down the result of a formula
Or if you want to use ROUNDDOWN, you can try
=MIN(100,ROUNDDOWN(100-(I2-0.75)*144,0)) for zero decimal places, or =MIN(100,ROUNDDOWN(100-(I2-0.75)*144,n)) to round down to n decimal places. -- David Biddulph "JE McGimpsey" wrote in message ... One way (to round down to the nearest integer): =MIN(100,INT(100-(I2-0.75)*144)) In article , Desdinova wrote: i am trying to get excel to round down (instead of round up) the result of the following formula: =IF((100-(((I2)-0.75)*144))100,100,(100-(((I2)-0.75)*144))). the input in I2 is 21:24, which is in the [h]:mm format. The answer is 79.6, but excel will by default display 80. I have noted the =ROUNDDOWN function, but i do not know if it can be applied within another formula. If so, then how? If not, then how can i get the result to round down? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to add conditions to an if statement
Look at Excel help for the syntax of the AND() function, then use that AND()
function as the condition part of your IF() function. Also, don't forget to specify the alternative output for when your IF() condition is NOT satisfied. If you don't specify an output, it will default to the Boolean FALSE, which may not be the output you want. -- David Biddulph "Desdinova" wrote in message ... ok, for some reason i cannot write a new post (maybe i can only have one?). So i will ask it he how can i add to an "if" function. As in, i want to say =IF M626 AND T6<135, "1st class" can i do this, and effectively add two conditions for one if statement? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to add conditions to an if statement
thanks for the reply. ok, so i need this information
=IF(AND(F626,M6<110),"FAIL",IF(AND(F6<26,M6<135), "FAIL",IF(AND(F626,M6<150),"3rd Class",IF(AND(F6<26,M6<175),"3rd Class",IF(AND(F626,M6<200),"2nd Class",IF(AND(F6<26,M6<225),"2nd Class", IF(AND(F626,M6=200),"1ST CLASS",IF(AND(F6<26,M6=225),"1ST CLASS")))))))) but i cant nest more than 7 functions, so it stops working after the "2nd class" statements. how can i write this better? "David Biddulph" wrote: Look at Excel help for the syntax of the AND() function, then use that AND() function as the condition part of your IF() function. Also, don't forget to specify the alternative output for when your IF() condition is NOT satisfied. If you don't specify an output, it will default to the Boolean FALSE, which may not be the output you want. -- David Biddulph "Desdinova" wrote in message ... ok, for some reason i cannot write a new post (maybe i can only have one?). So i will ask it he how can i add to an "if" function. As in, i want to say =IF M626 AND T6<135, "1st class" can i do this, and effectively add two conditions for one if statement? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to round down the result of a formula
I get 79.6. You must have the cell formatted to show 0 decimals.
If you want integer values, use =IF((100-(((I2)-0.75)*144))100,100,ROUNDDOWN((100-(((I2)-0.75)*144)),0)) this gives 79 with you I2 value -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Desdinova" wrote in message ... i am trying to get excel to round down (instead of round up) the result of the following formula: =IF((100-(((I2)-0.75)*144))100,100,(100-(((I2)-0.75)*144))). the input in I2 is 21:24, which is in the [h]:mm format. The answer is 79.6, but excel will by default display 80. I have noted the =ROUNDDOWN function, but i do not know if it can be applied within another formula. If so, then how? If not, then how can i get the result to round down? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to add conditions to an if statement
ok, i figured it all out, but now im running into another problem: i can only
have 7 functions nested, and i need to write 8. this is the formula: =IF(AND(F626,M6<110),"FAIL",IF(AND(F6<26,M6<135), "FAIL",IF(AND(F626,M6<150),"3rd Class",IF(AND(F6<26,M6<175),"3rd Class",IF(AND(F626,M6<200),"2nd Class",IF(AND(F6<26,M6<225),"2nd Class", IF(AND(F626,M6=200),"1ST CLASS",IF(AND(F6<26,M6=225),"1ST CLASS")))))))) it works all the way up until the eighth nested function, and then it wont work. is there any way to rewrite it? thanks for the help! "David Biddulph" wrote: Look at Excel help for the syntax of the AND() function, then use that AND() function as the condition part of your IF() function. Also, don't forget to specify the alternative output for when your IF() condition is NOT satisfied. If you don't specify an output, it will default to the Boolean FALSE, which may not be the output you want. -- David Biddulph "Desdinova" wrote in message ... ok, for some reason i cannot write a new post (maybe i can only have one?). So i will ask it he how can i add to an "if" function. As in, i want to say =IF M626 AND T6<135, "1st class" can i do this, and effectively add two conditions for one if statement? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to add conditions to an if statement
=IF(F1026,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd
Class","1ST CLASS"))),IF(F10<26,IF(M10<135,"FAIL",IF(M10<175," 3rd Class",IF(M10<225,"2nd Class","1ST CLASS"))),"Undefined")) as you still haven't defined the results for all conditions (such as if F10=26). If you want F10=26 to be grouped with one of the other groups you could try either =IF(F1026,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS")))) or =IF(F10=26,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS")))) -- David Biddulph "Desdinova" wrote in message ... ok, i figured it all out, but now im running into another problem: i can only have 7 functions nested, and i need to write 8. this is the formula: =IF(AND(F626,M6<110),"FAIL",IF(AND(F6<26,M6<135), "FAIL",IF(AND(F626,M6<150),"3rd Class",IF(AND(F6<26,M6<175),"3rd Class",IF(AND(F626,M6<200),"2nd Class",IF(AND(F6<26,M6<225),"2nd Class", IF(AND(F626,M6=200),"1ST CLASS",IF(AND(F6<26,M6=225),"1ST CLASS")))))))) it works all the way up until the eighth nested function, and then it wont work. is there any way to rewrite it? thanks for the help! "David Biddulph" wrote: Look at Excel help for the syntax of the AND() function, then use that AND() function as the condition part of your IF() function. Also, don't forget to specify the alternative output for when your IF() condition is NOT satisfied. If you don't specify an output, it will default to the Boolean FALSE, which may not be the output you want. -- David Biddulph "Desdinova" wrote in message ... ok, for some reason i cannot write a new post (maybe i can only have one?). So i will ask it he how can i add to an "if" function. As in, i want to say =IF M626 AND T6<135, "1st class" can i do this, and effectively add two conditions for one if statement? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to add conditions to an if statement
ok, im obviously not near as advanced as you are, so try not to get
frustrated. the problem is (still), that i have two parameters for four different formulas, which totals 8 operations. i need to make a =IF(F1026, and has a score M10110,"FAIL", but i also need a different age group =IF(F10<=26, and has a score M10135,"FAIL". but there is also a 1st,2nd, and 3rd class score for each age group. The way i am trying to do it : =IF(AND(F626,M6<110),"FAIL",IF(AND(F6<26,M6<135), "FAIL",IF(AND(F626,M6<150),"3rd Class",IF(AND(F6<26,M6<175),"3rd Class",IF(AND(F626,M6<200),"2nd Class",IF(AND(F6<26,M6<225),"2nd Class", IF(AND(F626,M6=200),"1ST CLASS",IF(AND(F6<26,M6=225),"1ST CLASS")))))))) works up until the eight nested function, since excel only allows 7. The solution you offered does not include the second age parameter F10<=26. I might be missing something very fundamental, so please excuse. And thank you again for the help. "David Biddulph" wrote: =IF(F1026,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(F10<26,IF(M10<135,"FAIL",IF(M10<175," 3rd Class",IF(M10<225,"2nd Class","1ST CLASS"))),"Undefined")) as you still haven't defined the results for all conditions (such as if F10=26). If you want F10=26 to be grouped with one of the other groups you could try either =IF(F1026,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS")))) or =IF(F10=26,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS")))) -- David Biddulph "Desdinova" wrote in message ... ok, i figured it all out, but now im running into another problem: i can only have 7 functions nested, and i need to write 8. this is the formula: =IF(AND(F626,M6<110),"FAIL",IF(AND(F6<26,M6<135), "FAIL",IF(AND(F626,M6<150),"3rd Class",IF(AND(F6<26,M6<175),"3rd Class",IF(AND(F626,M6<200),"2nd Class",IF(AND(F6<26,M6<225),"2nd Class", IF(AND(F626,M6=200),"1ST CLASS",IF(AND(F6<26,M6=225),"1ST CLASS")))))))) it works all the way up until the eighth nested function, and then it wont work. is there any way to rewrite it? thanks for the help! "David Biddulph" wrote: Look at Excel help for the syntax of the AND() function, then use that AND() function as the condition part of your IF() function. Also, don't forget to specify the alternative output for when your IF() condition is NOT satisfied. If you don't specify an output, it will default to the Boolean FALSE, which may not be the output you want. -- David Biddulph "Desdinova" wrote in message ... ok, for some reason i cannot write a new post (maybe i can only have one?). So i will ask it he how can i add to an "if" function. As in, i want to say =IF M626 AND T6<135, "1st class" can i do this, and effectively add two conditions for one if statement? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to add conditions to an if statement
I don't understand that you say:
"The solution you offered does not include the second age parameter F10<=26" Isn't that covered in the part of my first formula that says: "... IF(F10<26,IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS"))) or in the second half of the 2nd and 3rd formulae where it says: "... IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS"))))" in the path that we get to when we don't satify the initial F1026 test? Given that you're now saying that F10=26 should be grouped with F10<26, the formula to use is my second one: =IF(F1026,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS")))) Have you tried that formula? Is it giving results different from what you wanted? -- David Biddulph "Desdinova" wrote in message ... ok, im obviously not near as advanced as you are, so try not to get frustrated. the problem is (still), that i have two parameters for four different formulas, which totals 8 operations. i need to make a =IF(F1026, and has a score M10110,"FAIL", but i also need a different age group =IF(F10<=26, and has a score M10135,"FAIL". but there is also a 1st,2nd, and 3rd class score for each age group. The way i am trying to do it : =IF(AND(F626,M6<110),"FAIL",IF(AND(F6<26,M6<135), "FAIL",IF(AND(F626,M6<150),"3rd Class",IF(AND(F6<26,M6<175),"3rd Class",IF(AND(F626,M6<200),"2nd Class",IF(AND(F6<26,M6<225),"2nd Class", IF(AND(F626,M6=200),"1ST CLASS",IF(AND(F6<26,M6=225),"1ST CLASS")))))))) works up until the eight nested function, since excel only allows 7. The solution you offered does not include the second age parameter F10<=26. I might be missing something very fundamental, so please excuse. And thank you again for the help. "David Biddulph" wrote: =IF(F1026,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(F10<26,IF(M10<135,"FAIL",IF(M10<175," 3rd Class",IF(M10<225,"2nd Class","1ST CLASS"))),"Undefined")) as you still haven't defined the results for all conditions (such as if F10=26). If you want F10=26 to be grouped with one of the other groups you could try either =IF(F1026,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS")))) or =IF(F10=26,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS")))) -- David Biddulph "Desdinova" wrote in message ... ok, i figured it all out, but now im running into another problem: i can only have 7 functions nested, and i need to write 8. this is the formula: =IF(AND(F626,M6<110),"FAIL",IF(AND(F6<26,M6<135), "FAIL",IF(AND(F626,M6<150),"3rd Class",IF(AND(F6<26,M6<175),"3rd Class",IF(AND(F626,M6<200),"2nd Class",IF(AND(F6<26,M6<225),"2nd Class", IF(AND(F626,M6=200),"1ST CLASS",IF(AND(F6<26,M6=225),"1ST CLASS")))))))) it works all the way up until the eighth nested function, and then it wont work. is there any way to rewrite it? thanks for the help! "David Biddulph" wrote: Look at Excel help for the syntax of the AND() function, then use that AND() function as the condition part of your IF() function. Also, don't forget to specify the alternative output for when your IF() condition is NOT satisfied. If you don't specify an output, it will default to the Boolean FALSE, which may not be the output you want. -- David Biddulph "Desdinova" wrote in message ... ok, for some reason i cannot write a new post (maybe i can only have one?). So i will ask it he how can i add to an "if" function. As in, i want to say =IF M626 AND T6<135, "1st class" can i do this, and effectively add two conditions for one if statement? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to add conditions to an if statement
i believe everything is satisfied now. like i previously stated, i probably
overlooked the formula the first time you posted it. my apologies. it is difficult because i am tyring to fit time to work on this spreadsheet while i am doing other things, so thats my justification, however much redundant. Thanks for your time, it is much appreciated. "David Biddulph" wrote: I don't understand that you say: "The solution you offered does not include the second age parameter F10<=26" Isn't that covered in the part of my first formula that says: "... IF(F10<26,IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS"))) or in the second half of the 2nd and 3rd formulae where it says: "... IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS"))))" in the path that we get to when we don't satify the initial F1026 test? Given that you're now saying that F10=26 should be grouped with F10<26, the formula to use is my second one: =IF(F1026,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS")))) Have you tried that formula? Is it giving results different from what you wanted? -- David Biddulph "Desdinova" wrote in message ... ok, im obviously not near as advanced as you are, so try not to get frustrated. the problem is (still), that i have two parameters for four different formulas, which totals 8 operations. i need to make a =IF(F1026, and has a score M10110,"FAIL", but i also need a different age group =IF(F10<=26, and has a score M10135,"FAIL". but there is also a 1st,2nd, and 3rd class score for each age group. The way i am trying to do it : =IF(AND(F626,M6<110),"FAIL",IF(AND(F6<26,M6<135), "FAIL",IF(AND(F626,M6<150),"3rd Class",IF(AND(F6<26,M6<175),"3rd Class",IF(AND(F626,M6<200),"2nd Class",IF(AND(F6<26,M6<225),"2nd Class", IF(AND(F626,M6=200),"1ST CLASS",IF(AND(F6<26,M6=225),"1ST CLASS")))))))) works up until the eight nested function, since excel only allows 7. The solution you offered does not include the second age parameter F10<=26. I might be missing something very fundamental, so please excuse. And thank you again for the help. "David Biddulph" wrote: =IF(F1026,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(F10<26,IF(M10<135,"FAIL",IF(M10<175," 3rd Class",IF(M10<225,"2nd Class","1ST CLASS"))),"Undefined")) as you still haven't defined the results for all conditions (such as if F10=26). If you want F10=26 to be grouped with one of the other groups you could try either =IF(F1026,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS")))) or =IF(F10=26,IF(M10<110,"FAIL",IF(M10<150,"3rd Class",IF(M10<200,"2nd Class","1ST CLASS"))),IF(M10<135,"FAIL",IF(M10<175,"3rd Class",IF(M10<225,"2nd Class","1ST CLASS")))) -- David Biddulph "Desdinova" wrote in message ... ok, i figured it all out, but now im running into another problem: i can only have 7 functions nested, and i need to write 8. this is the formula: =IF(AND(F626,M6<110),"FAIL",IF(AND(F6<26,M6<135), "FAIL",IF(AND(F626,M6<150),"3rd Class",IF(AND(F6<26,M6<175),"3rd Class",IF(AND(F626,M6<200),"2nd Class",IF(AND(F6<26,M6<225),"2nd Class", IF(AND(F626,M6=200),"1ST CLASS",IF(AND(F6<26,M6=225),"1ST CLASS")))))))) it works all the way up until the eighth nested function, and then it wont work. is there any way to rewrite it? thanks for the help! "David Biddulph" wrote: Look at Excel help for the syntax of the AND() function, then use that AND() function as the condition part of your IF() function. Also, don't forget to specify the alternative output for when your IF() condition is NOT satisfied. If you don't specify an output, it will default to the Boolean FALSE, which may not be the output you want. -- David Biddulph "Desdinova" wrote in message ... ok, for some reason i cannot write a new post (maybe i can only have one?). So i will ask it he how can i add to an "if" function. As in, i want to say =IF M626 AND T6<135, "1st class" can i do this, and effectively add two conditions for one if statement? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get formula result of 3.56 to round up to 4 automatic? | Excel Worksheet Functions | |||
| How do I get formula result of 3.56 to round up to 4 automatic? | Excel Worksheet Functions | |||
| how do I round up a result of a formula in Excel | Excel Worksheet Functions | |||
| How to round up the result in a cell containing formula | Excel Worksheet Functions | |||
| How do I round up the result of a sum | Excel Worksheet Functions | |||
Linear Mode
