Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I want to express any number with a fraction to roundup to the nearest 1/3, 2/3 or 1. Example: 121.001- 121.333= 121 1/3 121.334- 121.666= 121 2/3 121.667- 121.999= 122 OR IF CELL A1= 131.001- 131.333 THEN RETURN 131 1/3 IF CELL A1= 131.334- 131.666 THEN RETURN 131 2/3 IF CELL A1= 131.667- 131.999 THEN RETURN 132
Solved by E. Q. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 10/12/2017 - 05:39
ok
Excelchat Expert 10/12/2017 - 05:40
Hello. How may I help you today?
User 10/12/2017 - 05:41
WANT TO TAKE ANY NUMBER WITH A DECIMAL AND ROUNDUP TO THE NEAREST 1/3, 2/3 OR 1
Excelchat Expert 10/12/2017 - 05:43
okay
Excelchat Expert 10/12/2017 - 05:43
one moment plz
User 10/12/2017 - 05:43
ok
Excelchat Expert 10/12/2017 - 05:47
Looks like a case of conditional formatting, .i.e, the cells format should be set to fraction first..and the scope to be chosen as 1/3, 2/3,1..based on the rounded values
User 10/12/2017 - 05:48
IF CELL A1= 131.001- 131.333 THEN RETURN 131 1/3
User 10/12/2017 - 05:48
IF CELL A1= 131.334- 131.666 THEN RETURN 131 2/3
User 10/12/2017 - 05:49
IF CELL A1= 131.667- 131.999 THEN RETURN 132
Excelchat Expert 10/12/2017 - 05:51
oka,one moment plz.
User 10/12/2017 - 05:51
oK
Excelchat Expert 10/12/2017 - 05:58
Ok, thanks for your time. This can be done using a formula and a reference column
User 10/12/2017 - 05:58
COOL
Excelchat Expert 10/12/2017 - 05:58
For ex, select a number say like 324.75
Excelchat Expert 10/12/2017 - 05:59
I'll type it up in my input column A as u see
Excelchat Expert 10/12/2017 - 05:59
I will take my output column as column E
Excelchat Expert 10/12/2017 - 06:00
Now,as we know. that output has to be in fractions of 1/3,2/3 and 1,lets set thye data type of column E as a fraction.
Excelchat Expert 10/12/2017 - 06:02
this can be done by home>number>fraction
Excelchat Expert 10/12/2017 - 06:02
clear till here??
User 10/12/2017 - 06:02
YES
User 10/12/2017 - 06:03
YES
Excelchat Expert 10/12/2017 - 06:03
Now, based on the value after the decimal point,the rounding fraction has to constrained to (1/3 or 2/3 or 1)
Excelchat Expert 10/12/2017 - 06:04
So,first get that decimal part seperately and print that in column B
Excelchat Expert 10/12/2017 - 06:04
this can be done by using =trunc function as written in B2 cell
Excelchat Expert 10/12/2017 - 06:04
=A1-TRUNC(A1)
Excelchat Expert 10/12/2017 - 06:04
Clear??
User 10/12/2017 - 06:04
YES
Excelchat Expert 10/12/2017 - 06:05
Now,lets write a nested if function for this value and set the respective outputs as we required.
Excelchat Expert 10/12/2017 - 06:06
and print it in next column.
Excelchat Expert 10/12/2017 - 06:06
I will now write the formula for that
Excelchat Expert 10/12/2017 - 06:10
=IF(0.001
Excelchat Expert 10/12/2017 - 06:11
in this formula, what we did is constraint the outputs based on value of this decima
Excelchat Expert 10/12/2017 - 06:11
clear?
User 10/12/2017 - 06:11
YES, VERY COOL
Excelchat Expert 10/12/2017 - 06:13
Now,we get the output as 1/3or 2/3 or 1 or 0, depending on the values in B2
Excelchat Expert 10/12/2017 - 06:13
Now,we get the output as 1/3or 2/3 or 1 or 0, depending on the values in B2
User 10/12/2017 - 06:14
YES, I SEE
Excelchat Expert 10/12/2017 - 06:14
so, for the final answe, we simply add the decimal value and this fraction value in cell c1
Excelchat Expert 10/12/2017 - 06:15
Now we set the format of E to fraction already
Excelchat Expert 10/12/2017 - 06:15
so by using function =sum(trunc(a1),c1), you can get the required output printed
Excelchat Expert 10/12/2017 - 06:16
clear??
User 10/12/2017 - 06:16
YES, LET ME WRITE THAT DOWN
Excelchat Expert 10/12/2017 - 06:16
Sure,go ahead and let me know.
Excelchat Expert 10/12/2017 - 06:17
there is also a direct formula. I will share that too. I gave this method so that u get clarity on the logic
Excelchat Expert 10/12/2017 - 06:24
Dear user, did it work?
Excelchat Expert 10/12/2017 - 06:24
I can help you incase of any troubles
User 10/12/2017 - 06:24
OK
Excelchat Expert 10/12/2017 - 06:25
=IF(B1<0.333,1/3,IF(B1<0.666,2/3,IF(B1<0.999,1,0)))
Excelchat Expert 10/12/2017 - 06:25
Did you enter this formula?
User 10/12/2017 - 06:26
ITS ALREADY IN C1 RIGHT?
Excelchat Expert 10/12/2017 - 06:26
The formula in C1
Excelchat Expert 10/12/2017 - 06:26
yes
User 10/12/2017 - 06:26
AND NOW IT CORRECTLY DISPLAYED 1
Excelchat Expert 10/12/2017 - 06:27
Oh, I was unawaere of that, may the networ issue made some delay in updating the formula Ive written after a correction some minutes ago
Excelchat Expert 10/12/2017 - 06:28
to assure you, the formula is as follows
Excelchat Expert 10/12/2017 - 06:28
=IF(B1<0.333,1/3,IF(B1<0.666,2/3,IF(B1<0.999,1,0)))
User 10/12/2017 - 06:28
YES, i HAVE THAT
User 10/12/2017 - 06:29
BUT HOW DO i GET 324.75 TO RETURN 325?
Excelchat Expert 10/12/2017 - 06:29
Now,in cell.E, please check the formula
User 10/12/2017 - 06:30
OK, BEAUTIFUL!
Excelchat Expert 10/12/2017 - 06:30
make sure that the column E is set to fraction by,.....home>number>fraction
User 10/12/2017 - 06:30
is THERE A WAY i CAN SAVE THIS SHEET?
Excelchat Expert 10/12/2017 - 06:30
Did that explanation help you attain at your answer?
User 10/12/2017 - 06:31
yes,
Excelchat Expert 10/12/2017 - 06:31
Great, I can send you an excel attachment with these formulas in blank sheet for you to save.
Excelchat Expert 10/12/2017 - 06:31
Do I?
User 10/12/2017 - 06:31
YES PLEASE
Excelchat Expert 10/12/2017 - 06:32
sure, one moment plz
Excelchat Expert 10/12/2017 - 06:35
please find the sheet
[Uploaded an Excel file]
Excelchat Expert 10/12/2017 - 06:36
you can also use this direct formula for the same,by setting column to fraction
Excelchat Expert 10/12/2017 - 06:36
=roundup((a1-int(a1))*3,0)/3
Excelchat Expert 10/12/2017 - 06:37
Is there anything else I can help with???
User 10/12/2017 - 06:37
NO, GREAT, THANK YOU VERY MUCH, NOW I CAN CREATE MY SPREADSHEET
Excelchat Expert 10/12/2017 - 06:38
Youre welcome have a great day
Excelchat Expert 10/12/2017 - 06:38
Please feel free to leave your feed bac for us
Excelchat Expert 10/12/2017 - 06:38
Thanks for choosing got it pro
Excelchat Expert 10/12/2017 - 06:38
Have a great day
Excelchat Expert 10/12/2017 - 06:38
Thankyou!
User 10/12/2017 - 06:39
THANK YOU!
Excelchat Expert 10/12/2017 - 06:39
My pleasure.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.