Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum cluster of cells based on values
Hello,
I have percentages in column A, and values in column B. The numbers are organised by groups separated by blank cells. I want to be able to sum clusters in B based on values in A with the following exception Sum all values Except when there are multiple occurences of 80%, stop at the first occurence of 80% For instance, From A2 to A5: 30%,60%,80%,80% From B2 to B5: 12,13,24,56 will give (12+13+24) in B1 A8:100% B8:45 will give (45) in B7 From A12 to A14:80%,80%,80% From B12 to B14:13,78,23 will give (13) in B11 Etc Any idea very welcome Thank you -- caroline |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum cluster of cells based on values
If I follow, I take it each value is something like this
Sum(block) - (count of 0.8's in bloc - 1) * 0.8 Assuming the above is right, you don't say if you want formulas in appropriate cells to do the calculation or for VBA to work out each value. Following attempts to do it both ways Sub Summer() Dim dbl As Double Dim i As Long, n As Long Dim rng As Range, ra As Range, cell As Range Dim sFla As String Const CFLA As String = _ "=SUM(R[-#]C[-1]:RC[-1])-(SUMPRODUCT(--(R[-#]C[-1]:RC[-1]=0.8))-1)*0.8" On Error Resume Next With Columns(1) Set rng = Union(.SpecialCells(xlCellTypeConstants, 1), _ .SpecialCells(xlCellTypeFormulas, 1)) End With On Error GoTo 0 If rng Is Nothing Then Exit Sub ' no values in col-A End If For Each ra In rng.Areas n = ra.Rows.Count sFla = Replace(CFLA, "#", n - 1) ra(1).Offset(n - 1, 1).FormulaR1C1 = sFla dbl = 0 i = 0 For Each cell In ra dbl = dbl + cell.Value If cell.Value = 0.8 Then i = i + 1 Next ra(1).Offset(n - 1, 2).Value = dbl - (i - 1) * 0.8 Next End Sub Regards, Peter T "caroline" wrote in message ... Hello, I have percentages in column A, and values in column B. The numbers are organised by groups separated by blank cells. I want to be able to sum clusters in B based on values in A with the following exception Sum all values Except when there are multiple occurences of 80%, stop at the first occurence of 80% For instance, From A2 to A5: 30%,60%,80%,80% From B2 to B5: 12,13,24,56 will give (12+13+24) in B1 A8:100% B8:45 will give (45) in B7 From A12 to A14:80%,80%,80% From B12 to B14:13,78,23 will give (13) in B11 Etc Any idea very welcome Thank you -- caroline |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to do a calculation based on the values in two cells | Excel Worksheet Functions | |||
Sum the values of cells based on fontcolor | Excel Worksheet Functions | |||
Sumif based on different values in different cells | Excel Discussion (Misc queries) | |||
conditional formating cells i Excel based on other cells values | Excel Worksheet Functions | |||
How to specify a range in VBA based on values in cells | Excel Programming |