adding minimum of a number range etc.

N

n00batW0rk

Hi everyone,

I have a little problem and hope someone can help. Thank you for
reading my thread and any feedback!

Cycle 1 45
Cycle 2 23
Cycle 3 12
Cycle 4 65
Cycle 5 69
Cycle 6 87
Cycle 7 124
Cycle 8 56
Cycle 9 93
Cycle 10 47

I need to write a formula that will take the smallest number of this
range, and then have it add to the lesser of the number either below it
or above it. In this case, it would be 12+23.


However, I would then need the formula to find the smallest number
again including the previous addition (12+23=35), and add it to the
lesser of the number below or above it.

I need to repeat this process until I have 5 cycles.

So the result would be:

Cylce 1 80 (12+23)+45
Cylce 2 134 65+69
Cylce 3 87
Cylce 4 180 56+124
Cylce 5 140 93+47


Once again, thank you for any feedback!!!
 
A

Alan Beban

n00batW0rk said:
Hi everyone,

I have a little problem and hope someone can help. Thank you for
reading my thread and any feedback!

Cycle 1 45
Cycle 2 23
Cycle 3 12
Cycle 4 65
Cycle 5 69
Cycle 6 87
Cycle 7 124
Cycle 8 56
Cycle 9 93
Cycle 10 47

I need to write a formula that will take the smallest number of this
range, and then have it add to the lesser of the number either below it
or above it. In this case, it would be 12+23.


However, I would then need the formula to find the smallest number
again . . . .
Click to expand...

FROM WHAT SET OF NUMBERS?

Alan Beban
 
N

n00batW0rk

Alan said:
*n00batW0rk wrote:

FROM WHAT SET OF NUMBERS?

Alan Beban
Click to expand...

Same set...

A1: Cycle 1, 45
A2: Cycle 2, 23
A3: Cycle 3, 12
A4: Cycle 4, 65
A5: Cycle 5, 69
A6: Cycle 6, 87
A7: Cycle 7, 124
A8: Cycle 8, 56
A9: Cycle 9, 93
A10: Cycle 10, 47

What I'm trying to do is "squish" the cycle with the least amount (of
sales) because the model that I have to export this data to only
accepts 5 of these cycles.

This is the formula I came up with,

B1: =match(min(A1:A10),A1:A10,0)

C1: =MIN(A1:A10)+MIN(INDIRECT("A"&B1+1),INDIRECT("A"&B1-1))

This works fine for cell C1....My problem is that, in cell C2, I cannot
get the formula to EXCLUDE "12" and "23" and INCLUDE the addition of
those 2 numbers (35). Sorry if this might be confusing.
 
N

n00batW0rk

Arvi said:
Hi

=SMALL(B1:B10,1)+SMALL(B1:B10,2)

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


Hi Avri,

Thank you for responding. However, this formula doesn't work for m
purposes. This formula adds the two smallest numbers in the whol
range
Click to expand...
 
A

Arvi Laanemets

Hi

Then you have to give more detailed explanation, what do you really want to
do. From your original posting I read out, that you wanted the sum of
smallest and next smallest numbers in some range - and exactly this my
formula is doing.

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


n00batW0rk said:
Arvi said:
Hi

=SMALL(B1:B10,1)+SMALL(B1:B10,2)

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


Hi Avri,

Thank you for responding. However, this formula doesn't work for my
purposes. This formula adds the two smallest numbers in the whole
range.
Click to expand...
Click to expand...
 
A

Alan Beban

n00batW0rk said:
This is the formula I came up with,

B1: =match(min(A1:A10),A1:A10,0)

C1: =MIN(A1:A10)+MIN(INDIRECT("A"&B1+1),INDIRECT("A"&B1-1))
Click to expand...

You can substitute the folowing single formula for the first cell,
assuming that your Cycle numbers are in A1:A10 and your data in B1:B10:

=MIN(B1:B10)+MIN(INDIRECT("B"&MATCH(MIN(B1:B10),B1:B10,0)-1),INDIRECT("B"&MATCH(MIN(B1:B10),B1:B10,0)+1))

Alan Beban
 
N

n00batW0rk

Arvi said:
*Hi

Then you have to give more detailed explanation, what do you reall
want to
do. From your original posting I read out, that you wanted the su
of
smallest and next smallest numbers in some range - and exactly thi
my
formula is doing.

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


*
Click to expand...




Cycle 1 45
Cycle 2 23
Cycle 3 12
Cycle 4 65
Cycle 5 69
Cycle 6 87
Cycle 7 124
Cycle 8 56
Cycle 9 93
Cycle 10 47
[/QUOTE]


Ok, first, i need the formula to find the smallest number (12), the
have it add to the lesser of the number above (23) or below (65). I
this case it would be 12+23 giving us 35. However i would then need th
formula to lookup the smallest number AGAIN, this time EXCLUDING th
numbers 12, 23, but INCLUDING 35, and then having it add to the lesse
of the number above (45) or below (65) it. This would be 35+45. Sorr
to be confusing but this is the best way i can explain it.

The formula can stop once everything is "squished" into 5 cycles.

Thanks again
 
A

Alan Beban

It remains confusing. After the formula calculates 35, what should the
10 numbers be changed to? All 10 of them.

Alan Beban

n00batW0rk wrote:
.....My problem is that, in cell C2, I cannot
 
N

n00batW0rk

Alan said:
*It remains confusing. After the formula calculates 35, what should
the
10 numbers be changed to? All 10 of them.

Alan Beban
*
Click to expand...
[/QUOTE]


Imagine the look on my face when my boss gave me this......


Cycle 1 45
Cycle 2 23
Cycle 3 12
Cycle 4 65
Cycle 5 69
Cycle 6 87
Cycle 7 124
Cycle 8 56
Cycle 9 93
Cycle 10 47

becomes:

Cycle 1 45
Cycle 2 35 (12+23)
Cycle 3 65
Cycle 4 69
Cycle 5 87
Cycle 6 124
Cycle 7 56
Cycle 8 93
Cycle 9 47

becomes:

Cycle 1 80 (35+45)
Cycle 2 65
Cycle 3 69
Cycle 4 87
Cycle 5 124
Cycle 6 56
Cycle 7 93
Cycle 8 47

becomes:

Cycle 1 80
Cycle 2 65
Cycle 3 69
Cycle 4 87
Cycle 5 124
Cycle 6 56
Cycle 7 140 (47+93)

becomes:

Cycle 1 80
Cycle 2 65
Cycle 3 69
Cycle 4 87
Cycle 5 180 (56+124)
Cycle 6 140

becomes:

Cycle 1 80
Cycle 2 134 (65+69)
Cycle 3 87
Cycle 4 180
Cycle 5 140
 
A

Alan Beban

If you start with the following in A1:B10, the somewhat cleaned up
recorded Macro 17 will get you to your result (watch for word wrap):
Cycle 1 45
Cycle 2 23
Cycle 3 12
Cycle 4 65
Cycle 5 69
Cycle 6 87
Cycle 7 124
Cycle 8 56
Cycle 9 93
Cycle 10 47

Sub Macro17()
Range("O1").FormulaR1C1 =
"=MIN(RC[-13]:R[9]C[-13])+MIN(INDIRECT(""B""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)-1),INDIRECT(""B""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)+1))"
Range("B1:B10").Copy Destination:=Range("C1:C10")
Range("O1").Copy
Range("C2").PasteSpecial Paste:=xlValues
Range("C3").Delete Shift:=xlUp
Range("C1:C9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("O1").Select
Selection.AutoFill Destination:=Range("O1:p1"), Type:=xlFillDefault
Range("P1").FormulaR1C1 = _
"=MIN(RC[-13]:R[9]C[-13])+MIN(INDIRECT(""C""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)-1),INDIRECT(""C""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)+1))"
Range("C1:C10").Select
Selection.Copy Destination:=Range("D1:D10")
Range("P1").Copy
Range("D1").PasteSpecial Paste:=xlValues
Range("D2").Delete Shift:=xlUp
Range("D1:D8").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("P1").AutoFill Destination:=Range("P1:Q1"), Type:=xlFillDefault
Range("Q1").Select
ActiveCell.FormulaR1C1 = _
"=MIN(RC[-13]:R[9]C[-13])+MIN(INDIRECT(""D""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)-1),INDIRECT(""D""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)+1))"
Range("D1:D10").Copy Destination:=Range("E1:E10")
Range("Q1").Copy
Range("E7").PasteSpecial Paste:=xlValues
Range("E8").Delete Shift:=xlUp
Range("E1:E7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("Q1").AutoFill Destination:=Range("Q1:R1"), Type:=xlFillDefault
Range("R1").FormulaR1C1 = _
"=MIN(RC[-13]:R[9]C[-13])+MIN(INDIRECT(""E""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)-1),INDIRECT(""E""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)+1))"
Range("E1:E10").Copy Destination:=Range("F1:F10")
Range("R1").Copy
Range("F5").PasteSpecial Paste:=xlValues
Range("F6").Delete Shift:=xlUp
Range("F1:F6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("R1").AutoFill Destination:=Range("R1:S1"), Type:=xlFillDefault
Range("S1").FormulaR1C1 = _
"=MIN(RC[-13]:R[9]C[-13])+MIN(INDIRECT(""F""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)-1),INDIRECT(""F""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)+1))"
Range("F1:F10").Copy Destination:=Range("G1:G10")
Range("S1").Copy
Range("G2").PasteSpecial Paste:=xlValues
Range("G3").Delete Shift:=xlUp
End Sub

Alan Beban



Imagine the look on my face when my boss gave me this......


Cycle 1 45
Cycle 2 23
Cycle 3 12
Cycle 4 65
Cycle 5 69
Cycle 6 87
Cycle 7 124
Cycle 8 56
Cycle 9 93
Cycle 10 47

becomes:

Cycle 1 45
Cycle 2 35 (12+23)
Cycle 3 65
Cycle 4 69
Cycle 5 87
Cycle 6 124
Cycle 7 56
Cycle 8 93
Cycle 9 47

becomes:

Cycle 1 80 (35+45)
Cycle 2 65
Cycle 3 69
Cycle 4 87
Cycle 5 124
Cycle 6 56
Cycle 7 93
Cycle 8 47

becomes:

Cycle 1 80
Cycle 2 65
Cycle 3 69
Cycle 4 87
Cycle 5 124
Cycle 6 56
Cycle 7 140 (47+93)

becomes:

Cycle 1 80
Cycle 2 65
Cycle 3 69
Cycle 4 87
Cycle 5 180 (56+124)
Cycle 6 140

becomes:

Cycle 1 80
Cycle 2 134 (65+69)
Cycle 3 87
Cycle 4 180
Cycle 5 140
[/QUOTE]
 
A

Alan Beban

The recorded below needs to be generalized. In particular, the algorithm
for the address of the previous minimums needs to be provided.

Alan Beban

Alan said:
If you start with the following in A1:B10, the somewhat cleaned up
recorded Macro 17 will get you to your result (watch for word wrap):
Cycle 1 45
Cycle 2 23
Cycle 3 12
Cycle 4 65
Cycle 5 69
Cycle 6 87
Cycle 7 124
Cycle 8 56
Cycle 9 93
Cycle 10 47

Sub Macro17()
Range("O1").FormulaR1C1 =
"=MIN(RC[-13]:R[9]C[-13])+MIN(INDIRECT(""B""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)-1),INDIRECT(""B""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)+1))"

Range("B1:B10").Copy Destination:=Range("C1:C10")
Range("O1").Copy
Range("C2").PasteSpecial Paste:=xlValues
Range("C3").Delete Shift:=xlUp
Range("C1:C9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("O1").Select
Selection.AutoFill Destination:=Range("O1:p1"), Type:=xlFillDefault
Range("P1").FormulaR1C1 = _
"=MIN(RC[-13]:R[9]C[-13])+MIN(INDIRECT(""C""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)-1),INDIRECT(""C""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)+1))"

Range("C1:C10").Select
Selection.Copy Destination:=Range("D1:D10")
Range("P1").Copy
Range("D1").PasteSpecial Paste:=xlValues
Range("D2").Delete Shift:=xlUp
Range("D1:D8").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("P1").AutoFill Destination:=Range("P1:Q1"), Type:=xlFillDefault
Range("Q1").Select
ActiveCell.FormulaR1C1 = _
"=MIN(RC[-13]:R[9]C[-13])+MIN(INDIRECT(""D""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)-1),INDIRECT(""D""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)+1))"

Range("D1:D10").Copy Destination:=Range("E1:E10")
Range("Q1").Copy
Range("E7").PasteSpecial Paste:=xlValues
Range("E8").Delete Shift:=xlUp
Range("E1:E7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("Q1").AutoFill Destination:=Range("Q1:R1"), Type:=xlFillDefault
Range("R1").FormulaR1C1 = _
"=MIN(RC[-13]:R[9]C[-13])+MIN(INDIRECT(""E""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)-1),INDIRECT(""E""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)+1))"

Range("E1:E10").Copy Destination:=Range("F1:F10")
Range("R1").Copy
Range("F5").PasteSpecial Paste:=xlValues
Range("F6").Delete Shift:=xlUp
Range("F1:F6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("R1").AutoFill Destination:=Range("R1:S1"), Type:=xlFillDefault
Range("S1").FormulaR1C1 = _
"=MIN(RC[-13]:R[9]C[-13])+MIN(INDIRECT(""F""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)-1),INDIRECT(""F""&MATCH(MIN(RC[-13]:R[9]C[-13]),RC[-13]:R[9]C[-13],0)+1))"

Range("F1:F10").Copy Destination:=Range("G1:G10")
Range("S1").Copy
Range("G2").PasteSpecial Paste:=xlValues
Range("G3").Delete Shift:=xlUp
End Sub

Alan Beban

*
Click to expand...


Imagine the look on my face when my boss gave me this......


Cycle 1 45
Cycle 2 23
Cycle 3 12
Cycle 4 65
Cycle 5 69
Cycle 6 87
Cycle 7 124
Cycle 8 56
Cycle 9 93
Cycle 10 47

becomes:

Cycle 1 45
Cycle 2 35 (12+23)
Cycle 3 65
Cycle 4 69
Cycle 5 87
Cycle 6 124
Cycle 7 56
Cycle 8 93
Cycle 9 47

becomes:

Cycle 1 80 (35+45)
Cycle 2 65
Cycle 3 69
Cycle 4 87
Cycle 5 124
Cycle 6 56
Cycle 7 93
Cycle 8 47

becomes:

Cycle 1 80 Cycle 2 65
Cycle 3 69
Cycle 4 87
Cycle 5 124
Cycle 6 56
Cycle 7 140 (47+93)

becomes:
Cycle 1 80 Cycle 2 65
Cycle 3 69
Cycle 4 87
Cycle 5 180 (56+124)
Cycle 6 140
becomes:

Cycle 1 80 Cycle 2 134 (65+69)
Cycle 3 87
Cycle 4 180 Cycle 5 140
Click to expand...
 
R

Ron Rosenfeld

Hi everyone,

I have a little problem and hope someone can help. Thank you for
reading my thread and any feedback!

Cycle 1 45
Cycle 2 23
Cycle 3 12
Cycle 4 65
Cycle 5 69
Cycle 6 87
Cycle 7 124
Cycle 8 56
Cycle 9 93
Cycle 10 47

I need to write a formula that will take the smallest number of this
range, and then have it add to the lesser of the number either below it
or above it. In this case, it would be 12+23.


However, I would then need the formula to find the smallest number
again including the previous addition (12+23=35), and add it to the
lesser of the number below or above it.

I need to repeat this process until I have 5 cycles.

So the result would be:

Cylce 1 80 (12+23)+45
Cylce 2 134 65+69
Cylce 3 87
Cylce 4 180 56+124
Cylce 5 140 93+47


Once again, thank you for any feedback!!!
Click to expand...

Try the VBA macro posted below.

To enter it, <alt><F11> opens the VB Editor.

Ensure that your current project is highlighted in the Project Explorer window,
then select Insert/Module.

Copy/Paste the code below.

To use the code, first select your data range (e.g. B1:B10). Then <alt><F8>
and run the macro "Squish".

If this does what you want, it should be modified to ensure that your selection
is valid, and there may be other changes that should be added, too.

As written, it will write the five cycles into the range one column over from
the selected range.

Also, the routine assumes all numbers are positive. If this is NOT the case,
some minor changes should be made.

=================================

Sub Squish()
Dim c As Range
Dim v() As Double
Dim i As Long, j As Long, p As Long, q As Long
Dim n As Double, k As Double

'assumes all entries are positive

ReDim v(Selection.Count - 1)

i = 0
For Each c In Selection
v(i) = c.Value
i = i + 1
Next c

Do Until UBound(v) = 4
n = Application.WorksheetFunction.Min(v)
j = Application.WorksheetFunction.Match(n, v, False) - 1
p = IIf(j = UBound(v), j - 1, j + 1)
q = IIf(j = 0, j + 1, j - 1)

k = Application.WorksheetFunction.Min(v(p), v(q))
v(j) = n + k

If v(p) >= v(q) Then
v(q) = -1
Else
v(p) = -1
End If

j = 0
For i = 0 To UBound(v) - 1
If v(i) >= 0 Then
v(i) = v(j)
j = j + 1
Else
v(i) = v(j + 1)
j = j + 2
End If
Next i
ReDim Preserve v(UBound(v) - 1)
Loop


Selection.Offset(0, 1).Clear
For i = 0 To 4
Selection.Offset(i, 1).Range("A1") = v(i)
Next i
End Sub
=====================================


--ron
 
A

Alan Beban

The code below will fail if the Option Base 1 Statement is in effect.
Making the two interlineated changes will obviate this minor defect.

Alan Beban
 
R

Ron Rosenfeld

The code below will fail if the Option Base 1 Statement is in effect.
Making the two interlineated changes will obviate this minor defect.
Click to expand...

Good point. Since my default is Base 0, I failed to explicitly state that.
But your changes make that issue moot.


--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

COUNTIF Cells in Range? 4
Number Search In Lottery Number Sets 25
Rearrange numbers from a column in a different order in a second c 14
need help finding most common number and so on 2
Dynamically reference a cell based on cell content 1
Return a value from a table using Column and row names as input? 8
WCG Stats Monday 20 September 2021 1
formula to display cell address 2

Top