Do until row is no more than 6 times row below

[bmaule]

Hi all,

I have 14 columns of number each having 20 rows. I would like to "normalize" the data so that no row has a value that is more than 6 times the row below.
I have use "do until" and "do while" macros but cannot get them to do what I want which is to keep changing the values until the condition is satisfied.

For example: if row1 =120; row2=90; row3=10; row4=1. The result should should be 120, 36, 6, 1. In others words the macro should change the same value several times until the condition is satisfied.

 

[PCL]

IS it what you want

Option Explicit


Sub Normalize()
Dim WkRg As Range
Dim LR  As Integer, I As Integer, LC  As Integer, J As Integer
Const Ratio  As Integer = 6
    Application.ScreenUpdating = False
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    For J = 1 To LC
        LR = Cells(Rows.Count, J).End(3).Row
        For I = LR To 2 Step -1
            If (Cells(I - 1, J) > Cells(I, J) * Ratio) Then Cells(I - 1, J) = Cells(I, J) * Ratio
        Next I
    Next J
    Application.ScreenUpdating = True
End Sub

 

[bmaule]

Thanks a mil. But cannot get it to work. My date is in column 237 and start at row 5.
I have tried to make these changes to you macro but it does not work.
Again thanks for your help.

 

[Peter_SSs]

if row1 =120; row2=90; row3=10; row4=1. The result should should be 120, 36, 6, 1. In others words the macro should change the same value several times until the condition is satisfied.

You might need to explain your logic/reasoning a bit more. Whilst I agree that the solution you suggest meets the requirements, so does 120, 29, 5, 1
Why couldn't that be the result?

What should the result be, and why, if the initial data is
500, 100, 10, 2

 

[PCL]

cannot get it to work

=> Can you explain the issue

My date is in column 237 and start at row 5.

=> See next code and adjust Const to your need

Option Explicit


Sub Normalize()
Dim WkRg As Range
Dim LR  As Integer, I As Integer, LC  As Integer, J As Integer
Const Ratio  As Integer = 6
Const FR = 5        '  First Row for Data
Const FC = 1        '  First Column for Data


    Application.ScreenUpdating = False
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    For J = FC To LC
        LR = Cells(Rows.Count, J).End(3).Row
        For I = LR To FR Step -1
            If (Cells(I - 1, J) > Cells(I, J) * Ratio) Then Cells(I - 1, J) = Cells(I, J) * Ratio
        Next I
    Next J
    Application.ScreenUpdating = True
End Sub

 

[bmaule]

Hi Peter, thanks for your response. to answer your question the result would be 432, 72, 12, 2 .

However, my current macro only changes the 100 (since it Ii more than 6*10) and does not change any other value.

What I want it to do is change 100 and then loop again to make other changes consistent with the change of the 100 to 72 and keep changing the other values until no value in the range is more than 6 times the value in the row below.

Finally, oftentimes my values include decimals and I will like to do this for columns 237-248 and rows 5 to 27.

Thanks

 

[Peter_SSs]

Hi Peter, thanks for your response. to answer your question the result would be 432, 72, 12, 2 .

OK, that answered my second question, but what about my first question?

Whilst I agree that the solution you suggest meets the requirements, so does 120, 29, 5, 1
Why couldn't that be the result?

oftentimes my values include decimals

Could we have an example of data and result for one of those columns too?

 

[PCL]

Could we have an example of data and result for one of those columns too?

OOOOhhh yes please !!!

 

[bmaule]

Hi Peter and PCL, thanks for your help.

To answer Peter's first question your numbers would satisfy the condition if they are the original values before calling the macro. And if they are then no values would be changes since each value is less than 6 times the value in the row below it. However, if the original values are 120, 90, 10, 1 any changes would result in multiple (6*) of the value not a multiple that is less than 6 times the value. So if the macro is working from the last row (27) to the top row (5), it should change the value 10 to become 6; would then change 90 to become 36; would not change 120 (since it is less then 36*6). However, if it is working from the top row to the last row, it would first change 90 to become to become 60 (since it is 6 times the row below); then change the 10 to 6; then having change the 10 to 6 it would then "reloop" to the 60 that was the original change and now change it to 36.
If this is confusing I apologize. Not sure how to post the data but here is an example:

[TABLE="width: 94"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5242.8400[/TD]
[/TR]
[TR]
[TD="align: right"]2.5200[/TD]
[/TR]
[TR]
[TD="align: right"]0.0400[/TD]
[/TR]
[TR]
[TD="align: right"]0.0180

After "normalizing" the [/TD]
[/TR]
</tbody>[/TABLE]
result should be 1.4400; 0.2400; 0.4000 and 0.01800
Again, thanks

 

[PCL]

I am really sorry but the code sent gives exactly the result you metion.
Assuming
A5 = 5242.84
A6 = 2.52
A7 =0.04
A8= 0.018