Re calculation of weights

E

erdem_ustun

New Member
Joined
Jun 19, 2018
Messages
12
Hi,

I have excel files.

There are different sections in this file.

There are different features under each section.
Unequal weights are available for these features.
The sum of each section is 10.
However, if there are no features under a section, the weight of the non-features needs to be proportionally distributed to other features existing under the same section.
How can I do this with VBA.
First I set all the features and weights by default. Later, if the cell were written "No" in the column next to the no features, I thought it would be necessary to re-distribute the weight in this section.

But I could not .

As sample data

[TABLE="width: 500"]
<tbody>[TR]
[TD]Nbr[/TD]
[TD]Section 1[/TD]
[TD]Default weights[/TD]
[TD]Status[/TD]
[TD]Reweighted[/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]Feature 1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3,75[/TD]
[/TR]
[TR]
[TD]1.2[/TD]
[TD]Feature 2[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]6,25[/TD]
[/TR]
[TR]
[TD]1.3[/TD]
[TD]Feature 3[/TD]
[TD]2[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Section 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[TD]Feature 1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2.2[/TD]
[TD]Feature 2[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Section 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.1[/TD]
[TD]Feature 1[/TD]
[TD]2[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.2[/TD]
[TD]Feature 2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3.3[/TD]
[TD]Feature 3[/TD]
[TD]2[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.4[/TD]
[TD]Feature 4[/TD]
[TD]4[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...

[/TD]
[TD].....[/TD]
[TD].....[/TD]
[TD]....[/TD]
[TD]....[/TD]
[/TR]
</tbody>[/TABLE]


I would like your help in this regard.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Here's my attempt:


Book1
ABCDE
1NbrSection 1Default WeightsStatusReweighted
21.1Feature 133.75
31.2Feature 256.25
41.3Feature 32No
5Section 2
62.1Feature 155
72.2Feature 255
8Section 3
93.1Feature 12No
103.2Feature 2210
113.3Feature 32No
123.4Feature 44No
Sheet1
Cell Formulas
RangeFormula
E2=IF(OR($A2="",$D2<>""),"",$C2/SUMPRODUCT(($A$2:$A$12>=INT($A2))*($A$2:$A$12$A2+1))*($D$2:$D$12=""),$C$2:$C$12)*10)


WBD
 
Upvote 0
Code: 
Public Sub ReweightFeatures()

Dim lastRow As Long
Dim thisRow As Long
Dim firstRow As Long
Dim weightTotal As Double

lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
firstRow = 0
weightTotal = 0
For thisRow = 2 To lastRow
    If Cells(thisRow, 1) = "" Then
        ProcessSection firstRow, thisRow - 1, weightTotal
        firstRow = 0
        weightTotal = 0
    Else
        If firstRow = 0 Then firstRow = thisRow
        If Cells(thisRow, "D").Value <> "No" Then weightTotal = weightTotal + Cells(thisRow, "C").Value
    End If
Next thisRow

End Sub
Private Sub ProcessSection(firstRow As Long, lastRow As Long, weightTotal As Double)

Dim thisRow As Long

For thisRow = firstRow To lastRow
    If Cells(thisRow, "D").Value = "No" Then
        Cells(thisRow, "E").Value = ""
    Else
        Cells(thisRow, "E").Value = Cells(thisRow, "C").Value / weightTotal * 10
    End If
Next thisRow

End Sub

WBD
 
Upvote 0
thanks your reply @wideboydixon

Excellent.
I've been trying to find a solution for about a week.
It's exactly what I want.

Could it be that we wanted to reduce the number of columns and be more efficient with one column?

Default Weights can not be changed. Constant values.

Can we make the table like this?
If it does not bother you, it will not take much time.

Could it make more sense if all the calculation / control is done in the weights column?

Default weights assigned to a two-dimension(Nbr,Default weights) and then
If do the operation above you can be less column and effective?



[TABLE="width: 273"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Nbr[/TD]
[TD]Sections[/TD]
[TD]Weights[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Section 1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]Feature 1[/TD]
[TD]3,75[/TD]
[/TR]
[TR]
[TD]1.2[/TD]
[TD]Feature 2[/TD]
[TD]6,25[/TD]
[/TR]
[TR]
[TD]1.3[/TD]
[TD]Feature 3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Section 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[TD]Feature 1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2.2[/TD]
[TD]Feature 2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Section 3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3.1[/TD]
[TD]Feature 1[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]3.2[/TD]
[TD]Feature 2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3.3[/TD]
[TD]Feature 3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]3.4[/TD]
[TD]Feature 4[/TD]
[TD]No


[/TD]
[/TR]
</tbody>[/TABLE]

Best regards...
 
Upvote 0
Before:


Book1
ABC
1NbrSection 1Weight
21.1Feature 15
31.2Feature 23
41.3Feature 3No
5Section 2
62.1Feature 15
72.2Feature 25
8Section 3
93.1Feature 12
103.2Feature 2No
113.3Feature 3No
123.4Feature 44
Sheet1


After:


Book1
ABC
1NbrSection 1Weight
21.1Feature 16.25
31.2Feature 23.75
41.3Feature 3No
5Section 2
62.1Feature 15
72.2Feature 25
8Section 3
93.1Feature 13.333333
103.2Feature 2No
113.3Feature 3No
123.4Feature 46.666667
Sheet1


Code: 
Public Sub ReweightFeatures()

Dim lastRow As Long
Dim thisRow As Long
Dim firstRow As Long
Dim weightTotal As Double

lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
firstRow = 0
weightTotal = 0
For thisRow = 2 To lastRow
    If Cells(thisRow, 1) = "" Then
        ProcessSection firstRow, thisRow - 1, weightTotal
        firstRow = 0
        weightTotal = 0
    Else
        If firstRow = 0 Then firstRow = thisRow
        If Cells(thisRow, "C").Value <> "No" Then weightTotal = weightTotal + Cells(thisRow, "C").Value
    End If
Next thisRow

End Sub
Private Sub ProcessSection(firstRow As Long, lastRow As Long, weightTotal As Double)

Dim thisRow As Long

For thisRow = firstRow To lastRow
    If Cells(thisRow, "C").Value <> "No" Then
        Cells(thisRow, "C").Value = Cells(thisRow, "C").Value / weightTotal * 10
    End If
Next thisRow

End Sub

WBD
 
Upvote 0
I appreciate your will and your knowledge.

I think I told you the last time I misunderstood.

If I try to tell you this way.First the C column (Weight) will be empty.

Before:

ABC
NbrSection 1Weight
Feature 1
Feature 2
Feature 3
Section 2
Feature 1
Feature 2
Section 3
Feature 1
Feature 2
Feature 3
Feature 4

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1.1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1.2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1.3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2.1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2.2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3.1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3.2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3.3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3.4[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet1​
After the user opens and writes the cells "No" in column C, calculations will be made.

When I saw the code you wrote, the logic came as follows.

I thought that when I run the code with the trigger(With Worksheet_change) when there is a change in column c,

when I write "No" for cells in column c, Recalculation is done.

Let's say I wrote first "No" after a certain period of time this feature provided and I will delete "No"


After:

ABC
NbrSection 1Weight
Feature 1
Feature 2
Feature 3No
Section 2
Feature 1
Feature 2
Section 3
Feature 1
Feature 2No
Feature 3No
Feature 4

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1.1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1.2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1.3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2.1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2.2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3.1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3.2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3.3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3.4[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet1​

Final:

<table cellpadding="2.5px" rules="all" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;="" border:="" 1px="" solid="" rgb(187,="" 187,="" 187);="" border-collapse:="" collapse;"="" width=""><colgroup><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD]Nbr[/TD]
[TD]Section 1[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1.1[/TD]
[TD]Feature 1[/TD]
[TD="align: right"]6.25[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]1.2[/TD]
[TD]Feature 2[/TD]
[TD="align: right"]3.75[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]1.3[/TD]
[TD]Feature 3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD]Section 2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]2.1[/TD]
[TD]Feature 1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]2.2[/TD]
[TD]Feature 2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD]Section 3[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]3.1[/TD]
[TD]Feature 1[/TD]
[TD="align: right"]3.333333[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]3.2[/TD]
[TD]Feature 2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]3.3[/TD]
[TD]Feature 3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]3.4[/TD]
[TD]Feature 4[/TD]
[TD="align: right"]6.666667[/TD]
[/TR]
</tbody></table>
Sheet1​

I hope that I did not keep you busy.
I really appreciate you.
my best regards..
 
Upvote 0
I wrote the final table incorrectly.
I'm sending the final table again for it so it does not look right.
Final:

ABC
NbrSection 1Weight
Feature 1
Feature 2
Feature 3No
Section 2
Feature 1
Feature 2
Section 3
Feature 1
Feature 2No
Feature 3No
Feature 4

<tbody>
[TD="align: right"]1.1[/TD]

[TD="align: right"]6.25[/TD]

[TD="align: right"]1.2[/TD]

[TD="align: right"]3.75[/TD]

[TD="align: right"]1.3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]2.1[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]2.2[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]3.1[/TD]

[TD="align: right"]3.333333[/TD]

[TD="align: right"]3.2[/TD]

[TD="align: right"]3.3[/TD]

[TD="align: right"]3.4[/TD]

[TD="align: right"]6.666667[/TD]

</tbody>
Click to expand...
 
Upvote 0
Doesn't make sense this way. How can you weight the features with no reference number to start with?

WBD
 
Upvote 0
Hi wideboydixon ;

wideboydixon said:
Doesn't make sense this way. How can you weight the features with no reference number to start with?

WBD
Click to expand...

is it be possible in the following way?

1. Column Nbr and default weights are initially defined as a row constant or array(Nbr,weights).

2.Then, if there is "No" in the weight column, we will write the default weights to the weight column by matching the first item in array(Nbr,weights) with the column Nbr.

3.later,If weights are written when the end of the section is finished, The new weights are calculated for the end of the rewrite weights.

What will be the benefit or activity of process.

The user will not have to know all the weights beforehand.

The user will only write "No" when the feature is not available.

The user will be able to change the "No" has previously written.

The user will be able to change the status of an existing feature.


With all respect
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
112
Peter_SSs
Peter_SSs
agerrard
  • Question Question
How to find the weighted average of a category?
Replies
1
Views
598
bebo021999
B
D
  • Solved
how to macro to auto sub-total and grand total of manpower plan used in the project
Replies
1
Views
270
david2005wang
D
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
S
  • Question Question
Auto-Populate Project Status
Replies
0
Views
548
Seoham
S

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top