Using VBA - Code To Truncate Equation To One Decimal Place

[JJVRANA]

Need VBA code to convert for the following Excel worksheet equation:

=Trunc((D26/B26)*0.96),1)

 

[Joe4]

Welcome to the Board!

If you have some value or calculation "x" that you want to Truncate in VBA to one decimal, you can do so like this:

FIX(x*10)/10

 

[JJVRANA]

Thank you.

 

[Joe4]

You are welcome.

 

[JJVRANA]

The Fix() eliminated the error. From all that I have read, Fix(x*10)/10 rounds NOT truncates.

In my Excel spreadsheet, using =Trunc((54.3/5)*0.96,1) which truncates to 10.4. The math answer is 10.43. Truncated to one decimal place = 10.4

Using the Fix(((54.3/5)*0.96)/10) gives 10.0.

How can I rectify this?

 

[StephenCrump]

Using the Fix(((54.3/5)*0.96)/10) gives 10.0.

This formula actually returns 1.

Joe4's solution was Fix(x*10)/10.

You have x= 54.3/5*0.96

So you need Fix(54.3/5*0.96*10)/10 = 10.4, i.e truncated (not rounded) to 1 dp as required.

 

[Joe4]

From all that I have read, Fix(x*10)/10 rounds NOT truncates.

That is not correct. FIX returns the INTEGER portion of a number. It does not round.
However, you want to return the first decimal. Since FIX returns the Integer portion (no decimals), we need to first multiply by 10, then apply the FIX function.
Then, after we get the answer, we can divide by 10 to get it back to the decimal number.

As Stephen pointed out, you had applied it incorrectly to your example. You did not multiply your formula by 10 before applying the FIX function.

 

[JJVRANA]

My mistake in understanding and applying the equation. Thanks for your help.

 

[Joe4]

No worries! You are welcome.