i'm looking for a better way to treat percentage

[mdavide]

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

 

[cherria]

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]

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]

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]

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?

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)
        'add unit
        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]

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]

this function sums the values of an array and returns the difference with another number.

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:

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]

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?

Thanks in advance

DM

 

[Harvey]

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]

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.