0
votes

I have an excel formula that is producing a lot of decimal places and I cannot reduce them using the format cell -> numbers -> decimal places options. Here is the formula.

Cell named V01_MIN V01_MIN =MIN(6:6) Has a value of 2

Cell named V01_MAX V01_MAX =MAX(6:6) Has a value of 1800

Cell named V01_A V01_A =1-V01_MIN*V01_B Has a value of 0.889877642

Cell named V01_B V01_B =99/(V01_MAX-V01_MIN) Has a value of 0.055061179

X6=723
X7=V01_A+V01_B*X6 (value of 40.69911012)
X8=1
X9=X7*X8 (value of 40.69911012)
X10=1
X11=X9*X10 (value of 40.69911012)
X13==CONCATENATE(X12,", ",X11)  

The final results of X13 are:

V01, 1162, 40.6991101223582

I want them to be:

V01, 1162, 40.7

I'm trying to figure out how to make this happen. I've already tried changing the cell formatting on all of these cells (including the final cell) to one decimal palce and that didn't work.

2

2 Answers

2
votes

Cell formatting and the actual number in the cell are two different things.

The cell formatting merely changes how the number is shown to you in the cell.

The actual number in the cell will still keep all precision of the number.

If you wish to have the last number rounded, consider this:

X13=CONCATENATE(X12,", ",ROUND(X11,1))

This will round the result in X11 to 1 decimal place before concatenating.

0
votes

By concatenating you are changing your data to text instead of a number and the number formats won't effect it. Generally you have two options

Either round within you concatenate function

X13==CONCATENATE(X12,", ",roound(X11,1)) 

or change it back to a number (easiest way is multiply by 1): Note this won't work in your case since you are joining text strings and variables but is useful to be aware of.

X13==CONCATENATE(X12,", ",X11)*1

and then you can format based on decimal places.