Adding a column of numbers based on an odd or even cell

[jUStPunkin]

I have a cell, number of collection devices. Based on that number of collection devices, I'm going to have a number of sections between those devices.

If the number of devices is 5, for example, I'm going to have sections 1, 2, 3, 2, 1 (where 1 is the same as 1, 2 is the same as 2, 3 is unique)

If the number of devices is 6, for example, I'm going to have sections 1, 2, 3, 3, 2, 1 (where 1 is the same as 1, 2 is the same as 2 and 3 is the same as 3)

I then do a variety of calculations based on a number for each of those sections.

The trouble I'm having is I need to do a sum on those calculations. So I'll have a column of numbers, and I need to sum 1*2, 2*2 3*1; or 1*2, 2*2, 3*2.

I know that if the number of devices is odd; it will be 1*2, 2*2 3*1

If the number of devices is even, it will be 1*2, 2*2, 3*2.

And, to add further complexity; the number of devices could be anywhere between 2 and 20.

I'm trying to be as clear as possible, but I'm not sure if it's clear. In the screenshot, the highlighted number is the number of devices.

For the blue numbers, which are inputs, I want to be able to enter only half of them, and then add them based on whether the number of devices is odd or even, if it is even, you double all the numbers, if it's odd, you double all but the last number.

What I'm thinking I might have to do is something like (and I am not sure my syntax is correct), with just multiple cases. I'm just wondering if there is an easier, shorter way to do this?

Public Sub NumberOfDevices()

Dim devices As Integer, result As Integer

devices = Range("b8").Value

Select Case devices

    Case Is = 3
        result = Sum(("A20"), ("A20"), ("A21"))
    Case Is = 4
        result = Sum(("A20"), ("A20"), ("A21"), ("A21"))
        
End Sub

 

[StephenCrump]

Do you mean something like this?

	A	B	C	D
1
2	Length	11		12
3
4	Result (VBA)	6.90		5.30
5	Result (Formula)	6.90		5.30
6	Check	6.90		5.30
7
8	Show ...	0.2		0.10
9		0.3		0.15
10		0.5		0.25
11		0.7		0.40
12		1.1		0.75
13		1.3		1.00
14	No need to show ...	1.1		1.00
15		0.7		0.75
16		0.5		0.40
17		0.3		0.25
18		0.2		0.15
19				0.10
Sheet1

Cell Formulas
Range	Formula
B4,D4	B4	=mytotal(B8:B13,B2)
B5,D5	B5	=2*SUMPRODUCT(B8:INDEX(B:B,ROW(A8)+B2/2-1))+ISODD(B2)*INDEX(B:B,ROW(A8)+B2/2)
B6,D6	B6	=SUM(B8:B19)

Function MyTotal(r As Range, N As Long) As Double

    MyTotal = 2 * Application.Sum(r) - (N Mod 2) * r((N + 1) / 2).Value

End Function

 

[jUStPunkin]

Do you mean something like this?

	A	B	C	D
1
2	Length	11		12
3
4	Result (VBA)	6.90		5.30
5	Result (Formula)	6.90		5.30
6	Check	6.90		5.30
7
8	Show ...	0.2		0.10
9		0.3		0.15
10		0.5		0.25
11		0.7		0.40
12		1.1		0.75
13		1.3		1.00
14	No need to show ...	1.1		1.00
15		0.7		0.75
16		0.5		0.40
17		0.3		0.25
18		0.2		0.15
19				0.10
Sheet1

Cell Formulas
Range	Formula
B4,D4	B4	=mytotal(B8:B13,B2)
B5,D5	B5	=2*SUMPRODUCT(B8:INDEX(B:B,ROW(A8)+B2/2-1))+ISODD(B2)*INDEX(B:B,ROW(A8)+B2/2)
B6,D6	B6	=SUM(B8:B19)

Function MyTotal(r As Range, N As Long) As Double

    MyTotal = 2 * Application.Sum(r) - (N Mod 2) * r((N + 1) / 2).Value

End Function

That looks like what I need. I'll play around with this today and see if I can make it work. If so, I'll mark this as a solution; if not, I'll be back with more questions

Thank you!!

 

[jUStPunkin]

I feel like I'm missing the easy part of this. I am able to make this work for odd cases with a little change

=2*SUMPRODUCT(B20:INDEX(B:B,ROW(A20)+B8/2))+ISODD(B8)*INDEX(B:B,ROW(A20)+B8/2+1)

But I can't make it work for even cases. It's this

 2*SUMPRODUCT(B20:INDEX(B:B,ROW(A20)+B8/2))

but I have to add the next cell as well, but not double it, just add the cell.

This should be the easy part, I just can't seem to figure how to get it into that array (and thank you for that, I am not really familiar with arrays, but I did learn a lot from this simple formula).

 

[StephenCrump]

Can you please post some sample layout, and the results you're looking for?

Preferably using the XL2BB add-in (which you can download here: XL2BB - Excel Range to BBCode) so we don't need to re-key.

 

[jUStPunkin]

Based on the data in B8 (number of downcomers), I need to be able to add the data in cells B8 through B25 (keeping in mind that this example only goes through B25, but they could go all the way through B40). If B8 is even; cells B8 through B25 are summed, and then multiplied x 2. If B8 is odd, cells B8 through B24 are added and doubled, and then B25 is added.

Is that clearer?

RedistPage.xlsm
	A	B	C	D
4	Service	Service
5	Date	Date
6	Designed by:	Designed
7	Comments:	Comments
8	Number of Downcomers	10
9		Liquid Rate (lb/hr)	Density (lb/ft3)
10	From Distributor	1,006,560	37.629	26,749.581
11	From Tray Above	3,077,699	35.190	87,459.477
12	Total			114,209.059
13
14	Collected From Above	15.58%	(Actual)
15	Net From Trough	1,486,066	36.806	40,375.768
16	Net From Above	2,598,193	35.190	73,833.291
17	Total			114,209.059
18
19	Panel	% of liquid feeding each panel (ideal)	Total Liquid to each panel	Liquid from tray above to each panel
20	1	2.630%	3,003.698	2,533.784
21	2	7.600%	8,679.888	8,863.817
22	3	9.870%	11,272.434	12,849.919
23	4	11.400%	13,019.833	15,474.511
24	5	12.240%	13,979.189	16,831.697
25	6	12.520%	14,298.974	17,279.562
26	7
27	8
28	9
29	10
30	11
31	12
32	13
33	14
34	15
35	16
36	17
37	18
38	19
39	20
40	21
41		112.52%	64,254.016	73,833.291
Redistribution Calculations

Cell Formulas
Range	Formula
D10	D10	=$B$10/$C$10
D11	D11	=$B$11/$C$11
D12,D17	D12	=SUM(D10:D11)
B15,D15	B15	=B10+$B$14*B11
C15:C16	C15	=B15/D15
B16,D16	B16	=B11*(1-$B$14)
C20:C40	C20	=IF(ISBLANK(B20),"",$D$17*B20)
D20:D40	D20	=IF(ISBLANK(B20),"",I20*$D$16)
B41	B41	=2*SUMPRODUCT($B$20:INDEX(B:B,ROW($A$20)+$B$8/2))+ISODD($B$8)*INDEX(B:B,ROW($A$20)+$B$8/2+1)
C41:D41	C41	=SUM(C20:C40)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L33:P35,L29:O30,G42:H42,C1:D3,Q:IV,P1:P24,L1:O26,B55:E65536,A1:A46,A49:A65536,F1:F13,F15:F41,L41:P65536,B1:B14,G1:H18,G41,E1:E54,F44:H65536,H19:H41,C8:D14,I:K,B15:D54	Expression	=NOT(CELL("protect",A1))	text	YES

Cells with Data Validation
Cell	Allow	Criteria
B11	Any value
C11	Any value
B14	Any value
B20:B40	Any value

 

[StephenCrump]

Thanks, yes that's clearer.

My original formula assumed that if number of downcomers = 10, say, you'd be taking twice the sum of 5 numbers. But that doesn't appear to be the case?

Will your numbers in B20:B40 always be an increasing sequence? If so, you could simplify to:

	A	B
8	Number of Downcomers	10
9
10
11
12
13
14
15
16
17
18
19	Panel	% of liquid feeding each panel (ideal)
20	1	2.63%
21	2	7.60%
22	3	9.87%
23	4	11.40%
24	5	12.24%
25	6	12.52%
26	7
27	8
28	9
29	10
30	11
31	12
32	13
33	14
34	15
35	16
36	17
37	18
38	19
39	20
40	21
41	Required result?	112.52%
Sheet2

Cell Formulas
Range	Formula
B41	B41	=2*SUM(B20:B40)-ISODD(B8)*MAX(B20:B40)

 

[jUStPunkin]

Oh my gosh; that's so simple! I just have to check to see that all columns (there are more that will have the same operation performed on them; I just didn't/could't share the whole spreadsheet. But I do believe everything should be increasing.

Yea, I know it was convoluted before, but I had to figure out what exactly I could share here; there are definitely some confidential calcs in our spreadsheets. But I think you nailed it

Thank you!!