Expert Solution - Excel IF Problems

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

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

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

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<A1<0.333,1/3,IF(0.333<A1<0.666,2/3,IF(0.666<A1<0.999,1,0)))

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

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