The client that I am working for wants me to do some custom formulas or write vba code that will round some items.
We have 3 formulas that produce the following numbers.
50.77%
24.62%
24.62%
Together they equal 100%. The customer does not want to show the decimal places. If you take the decimal places out you get
51%, 25%, 25% which equal 101%. They want me to do some type of formula that will round up or down. For example... it would look at the largest decimal place and round that number up... if it did not equal 100%, it would look at the second number with the largest decimal and round it up, so on so forth, until it is the whole numbers equal 100%
Below is the example they gave me on what they wanted me to do. I have never done any type of math functions like this in excel and do not know where to even start.
We have 3 numbers that there sum is 100
X1,X2,X3
The number without the decimal(down round the numbers)
Y1,Y2,Y3
Summarize only the digits of the 3 numbers
sum
While sum<100
{
Increase by one the number(y1,y2,y2) that is decimal is the biggest (x1,x2,x3) and round down is X number
}
Example
X1 =33.94 x2=32.98 x3= 33.06
Round it down
Y1=33 y2=32 y3 =33
Sum of y is 98
Increase by 1 the y that is x decimal is the biggest
In this example X2 decimal is the biggest
So Y2=Y2+1 and X2=RoundDown(X2)
Now
X1 =33.94 x2=32.0 x3= 33.06
Y1=33 y2=33 y3 =33
The sum of y now is 99 so we need to continue
Increase by 1 the y that is x decimal is the biggest
In this example X1 decimal is the biggest
Now
X1 =33.0 x2=32.0 x3= 33.06
Y1=34 y2=33 y3 =33
The sum of y now is 100 so we stop and present to the user: Y1=34 y2=33 y3 =33
cinstanl
Bookmarks