| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
IF statements
I wish to create a cell that gives a rounded up/rounded down figure i.e.
Assume figure of 20.38 The number before the decimal point will be variable. The number after the decimal point will be subject to the following conditions:- If < .124 then round down to next whole 0.5 (if 20.38 then figure to be 20.0) If .124 then round up to next whole 0.5 (if 20.38 then figure to be 20.5) If < .624 then round down to next whole 0.5 (if 20.45 then figure to be 20.5) If .624 then round up to next whole 0.5 (if 20.72 then figure to be 21.0) How do I write the formula please? |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
IF statements
Hi Dave
maybe =IF(OR(MOD(A1,1)0.624,AND(MOD(A1,1)0.124,MOD(A1, 1)<=0.5)) ,CEILING(A1,0.5),FLOOR(A1,0.5)) -- Regards Roger Govier "Dave in Ampthill" <Dave in wrote in message ... I wish to create a cell that gives a rounded up/rounded down figure i.e. Assume figure of 20.38 The number before the decimal point will be variable. The number after the decimal point will be subject to the following conditions:- If < .124 then round down to next whole 0.5 (if 20.38 then figure to be 20.0) If .124 then round up to next whole 0.5 (if 20.38 then figure to be 20.5) If < .624 then round down to next whole 0.5 (if 20.45 then figure to be 20.5) If .624 then round up to next whole 0.5 (if 20.72 then figure to be 21.0) How do I write the formula please? |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
IF statements
Hi Roger,
works perfectly so far. Many thanks for the prompt reply Dave "Roger Govier" wrote: Hi Dave maybe =IF(OR(MOD(A1,1)0.624,AND(MOD(A1,1)0.124,MOD(A1, 1)<=0.5)) ,CEILING(A1,0.5),FLOOR(A1,0.5)) -- Regards Roger Govier "Dave in Ampthill" <Dave in wrote in message ... I wish to create a cell that gives a rounded up/rounded down figure i.e. Assume figure of 20.38 The number before the decimal point will be variable. The number after the decimal point will be subject to the following conditions:- If < .124 then round down to next whole 0.5 (if 20.38 then figure to be 20.0) If .124 then round up to next whole 0.5 (if 20.38 then figure to be 20.5) If < .624 then round down to next whole 0.5 (if 20.45 then figure to be 20.5) If .624 then round up to next whole 0.5 (if 20.72 then figure to be 21.0) How do I write the formula please? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
IF statements
If you return to this thread, another possibility (if you have the Analysis
ToolPak Add-In installed) is this... =MROUND(MROUND(A1,0.25),0.5) -- Rick (MVP - Excel) "Dave in Ampthill" <Dave in wrote in message ... I wish to create a cell that gives a rounded up/rounded down figure i.e. Assume figure of 20.38 The number before the decimal point will be variable. The number after the decimal point will be subject to the following conditions:- If < .124 then round down to next whole 0.5 (if 20.38 then figure to be 20.0) If .124 then round up to next whole 0.5 (if 20.38 then figure to be 20.5) If < .624 then round down to next whole 0.5 (if 20.45 then figure to be 20.5) If .624 then round up to next whole 0.5 (if 20.72 then figure to be 21.0) How do I write the formula please? |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
IF statements
Very neat, Rick!
-- Regards Roger Govier "Rick Rothstein" wrote in message ... If you return to this thread, another possibility (if you have the Analysis ToolPak Add-In installed) is this... =MROUND(MROUND(A1,0.25),0.5) -- Rick (MVP - Excel) "Dave in Ampthill" <Dave in wrote in message ... I wish to create a cell that gives a rounded up/rounded down figure i.e. Assume figure of 20.38 The number before the decimal point will be variable. The number after the decimal point will be subject to the following conditions:- If < .124 then round down to next whole 0.5 (if 20.38 then figure to be 20.0) If .124 then round up to next whole 0.5 (if 20.38 then figure to be 20.5) If < .624 then round down to next whole 0.5 (if 20.45 then figure to be 20.5) If .624 then round up to next whole 0.5 (if 20.72 then figure to be 21.0) How do I write the formula please? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If And statements -- HELP! | Excel Worksheet Functions | |||
| If then statements | Excel Discussion (Misc queries) | |||
| how to do if and or else statements | Excel Worksheet Functions | |||
| IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
| if statements | Excel Worksheet Functions | |||
Linear Mode
