# i'm looking for a better way to treat percentage

#### mdavide

##### Board Regular
hi all,

well I don't have a real problem but just a curiosity: i'm looking for a better way to treat some percentage.

i'm working with an array, like this one:

2 3 1 2

the sum is 7 wich I consider like a net value; I have another input, that is a gross value (for this ex I say 10).

The problem is: I have to split the difference between the net and the gross value (10-7=3) inside the array in a proportional way.

the problem seems so simple, like I tought before, but I'm not able to trait it without using a lot of heavy code lines. there are problems with integer values, with approximation and with the final sum value that might be exactly 10.

I'm lookink for vba suggestion, I hope that what i wrote is enough clear.

many thanks.

DM

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### cherria

##### Well-known Member
If your gross value was in A1 and the 4 values above were in A2:A5

then in B2:B5 enter

=(((\$A\$1-SUM(\$A\$2:\$A\$5))/SUM(\$A\$2:\$A\$5))*\$A\$2:\$A\$5)

and press CTRL+SHIFT+ENTER

this array formula gives the distribution of the difference using a weighted average.

#### Harvey

##### Well-known Member
I'm not completely sure what you want though.
Say you have this:

2 3 1 2 - - - - - - 10 - - - - - x

now x has to be filled in with 10-(2+3+1+2=7)=3?
why not use a formula for that:

=H1-SUM(A1:G1)

assuming that you have your values in A1:G1, and the gross value in H1, you can put this in I1.

#### mdavide

##### Board Regular
thankx for your suggestions, but I'm looking for a vba code 'cause this problem is a part of a bigger program; the array isn't in a sheet.

the array with sum 7 (net value) and gross value 10

2 3 1 2

has to become something like this one

3 5 1 2

that sum 10

DM

#### mdavide

##### Board Regular
this one below is similar to the code that I use; It's seem to me much heavy and nasty and I don't like it but i'm not able to improve it. I don't know if I choose a wrong way to do it.

any ideas how to improve it, if it is possible?

Code:
``````For i = 1 To 7
' I have 7 array with different values, different net value and different gross value
' the code below is for a single array that I have to trait

ReDim pesoturno(Range("ESIGENZE").Rows.Count)
ReDim turnodainserireintero(Range("ESIGENZE").Rows.Count)
ReDim turnodainserire(Range("ESIGENZE").Rows.Count)

For j = 1 To Range("ESIGENZE").Rows.Count ' for each value in the array
pesoturno(j) = Range("ESIGENZE").Cells(j, i).Value / coperturegiorno(i) ' this is the weight or a single element of the array (per cent)
turnodainserire(j) = coperturegiornomancanti(i) * pesoturno(j) ' this is the double value to add with the elements
turnodainserireintero(j) = Round(coperturegiornomancanti(i) * pesoturno(j)) ' this is the integer value to add with the elements
Next
'if the difference between gross and net value is different from the sum of integer values that I add before
' say "turnodainserireintero", I had to modify the array another time
' this problem occurs when you round the double value, so I had to add or delete
' some units
While WorksheetFunction.Sum(turnodainserireintero) <> coperturegiornomancanti(i)
If WorksheetFunction.Sum(turnodainserireintero) < coperturegiornomancanti(i) Then
maxx = 0
For j = 1 To Range("ESIGENZE").Rows.Count
'I search the biggest decimal value under 0.5 and add 1 to the relative integer
If (turnodainserire(j) - Int(turnodainserire(j))) <= 0.5 And turnodainserire(j) - Int(turnodainserire(j)) > maxx Then
indice = j
maxx = turnodainserire(j) - Int(turnodainserire(j))
End If
Next
turnodainserireintero(indice) = CInt(turnodainserire(indice)) + 1
ElseIf WorksheetFunction.Sum(turnodainserireintero) > coperturegiornomancanti(i) Then
'delete unit
minn = 1
For j = 1 To Range("ESIGENZE").Rows.Count
'I search the smallest decimal value over 0.5 and subtract 1 to the relative integer
If turnodainserire(j) - Int(turnodainserire(j)) >= 0.5 And turnodainserire(j) - Int(turnodainserire(j)) <= minn Then
indice = j
minn = turnodainserire(j) - Int(turnodainserire(j))
End If
Next
turnodainserireintero(indice) = CInt(turnodainserire(indice)) - 1
End If
Wend
Next``````

many thanks

#### Harvey

##### Well-known Member
i didnt really look at this code of yours, but is there any condition to which integer in the array has to be modified in order to make the whole sum 10, or can it just be any random integer in the array?

#### Harvey

##### Well-known Member
this function sums the values of an array and returns the difference with another number.

Code:
``````Function ArrayDifference(thearray() As Integer, comparedto As Integer) As Integer

Dim i, total As Integer
For i = LBound(thearray) To UBound(thearray())
total = total + thearray(i)
Next i
ArrayDifference = comparedto - total

End Function``````

example:

Code:
``````Sub x()

Dim myarray(10) As Integer
Dim thedifference As Integer

myarray(1) = 1
myarray(2) = 3
myarray(3) = 4
myarray(4) = 2

thedifference = ArrayDifference(myarray, 15)
MsgBox (thedifference)

End Sub``````

#### mdavide

##### Board Regular
Harvey, thanks for your support but it's not what I'm looking for, maybe I haven't been clear before. I try to explain better.

I have two inputs, an array and a gross value:

array= 2 3 2 1
gross =10

sum (array) is the net value, that is 8 (before i wrote 7.... it 's my fault, it seems that i'm no good with simple addition!)

if sum (array) = gross there is no problem, well behaved. but if the sum(array) is smaller then gross value, I have to modify the array. How?

I calculate the weight (per cent) of every elements inside the array, that is
2/8 = 0.25
3/8 = 0.375
2/8 = 0.25
1/8 = 0.125

the difference between gross and net value is 10-8 = 2

so, what I want to do is : REDISTRIBUTE this value (2) in a PROPORTIONAL WAY inside the array in order to obtain sum(array)=gross value.

so I make round product like this

round(0.25*2) = 1
round(0.375*2)= 1
round(0.25*2)= 1
round(0.125*2)= 0

so I add these new values with the old array

2 + 1 = 3
3 + 1 = 4
2 + 1 = 3
1 + 0 = 1

but doing only these actions, I can't reach a solution because the sum of new array (say: 3+4+3+1=11) isn't equal to the gross value (10).
this is the reason why i wrote the ugly code above.

the question is: there is a way to improve my code? or I have to change completely my idea?

DM

#### Harvey

##### Well-known Member
Okay, now I undestrand the problem. It looks pretty complicated, but I am sure there is a small solution for this. I'll report back when I have looked it over.

#### Harvey

##### Well-known Member
Is it very important that all values are completely equally divided?

You could use a counter to look if you reach the gross value when adding to the numbers in the array.
-------------amount to spend
---------------2
1 (+1)-------1
2 (+1)-------0 -> end
3
1

would end up with 2, 3, 3, 1.

In the other case of dividing it equally you would have to sort the array first,
and put the numbers back in place later. This would be pretty annoying work, unless it is strictly neccessary.

Replies
0
Views
750
Replies
6
Views
426
Replies
3
Views
1K
Replies
0
Views
266
Replies
8
Views
1K

1,181,623
Messages
5,931,016
Members
436,771
Latest member
kwicol

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back