i'm looking for a better way to treat percentage

mdavide

Board Regular
Joined
Oct 19, 2004
Messages
211
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

cherria

Well-known Member
Joined
Nov 17, 2004
Messages
708
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
Joined
Nov 18, 2004
Messages
953
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
Joined
Oct 19, 2004
Messages
211
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
Joined
Oct 19, 2004
Messages
211

ADVERTISEMENT

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)
        '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

Well-known Member
Joined
Nov 18, 2004
Messages
953
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
Joined
Nov 18, 2004
Messages
953

ADVERTISEMENT

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
Joined
Oct 19, 2004
Messages
211
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

Well-known Member
Joined
Nov 18, 2004
Messages
953
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
Joined
Nov 18, 2004
Messages
953
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.
 

Forum statistics

Threads
1,148,055
Messages
5,744,539
Members
423,882
Latest member
Seeham

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top