CODEWITHSUNDEEP
excelif-statementrounding
Dylan R.
Dylan R.

Reputation: 39

Using ROUND after Nested IF Statements in Excel

I am working on a spreadsheet for a client that calculates values and shows them as fractions after a good amount of deductions. We were running some test after I had it all set and ready and they ran a certain number through the spread sheet. I'll try to beak it down as easily as possible (any value with brackets is user input. So Width is [94.5(94 1/2)] that number get subtracted by 7.5312(7 17/32 that equals 86.9688(86 31/32) that number is then divided by [3] which then equals 28.9896(28 95/96) This is where the problem is though. 95/96 is not a "real" fraction is there a way I can round numbers like this down to 64ths? They were expecting 63/64ths Even though the math is correct they need it to round down in those cases.

The If statement is this:

=IF(E4=1,(K4-F19)/1,IF(E4=2,(K4-G19)/2,IF(E4=3,(K4-H19)/3,IF(E4=4,(K4-I19)/4,IF(E4=5,(K4-J19)/5,IF(E4=6,(K4-K19)/6,IF(E4=7,(K4-L19)/7,IF(E4=8,(K4-M19)/8,IF(E4=9,(K4-N19)/9,IF(E4=10,(K4-O19)/10,0))))))))))

This is a single part of the IF statement:

=IF(E4=1,(K4-F19)/1

Is there a way around this or are they SOL(Sorta Outta Luck)?

Thanks for any insight.

Upvotes: 1

Views: 1445

Answers (1)

pnuts
pnuts

Reputation: 59450

Assuming in A1, please try:

=ROUNDDOWN(A1*64,0)/64

with suitable formatting.

This ensures that all results are multiples of 1/64.

Upvotes: 2

Related Questions