interrelated constraints

[JBoulton]

Hi, all!

The "almost final" answer to my analysis is contained in six cells, sa
a1 - f1 with the sum of those in g1. The last step is to be sure tha
none of the cells a1 - f1 exceeds 20% of the total in g1. A1 - F1 ca
get smaller than they are now, but not bigger.

I've been using Excel for years and just can't work this one out. An
ideas are appreciated.

TIA
Ji

 

[Frank Kabel]

Hi
you could use a helper row (e.g. row 29 with the formula in A2:
=IF(A1>SUM($A$1:$F$1)*0.2,"Too much","")
and copy this formula for all columns

 

[Marie]

It depends on what you want the output to look like. For
example, if you want 'error' to display underneath each
cell that exceeds 20% of the total, this would work, given
that g1 reflects the summary total of a1:f1:
Formula in cell A2:
=IF(A1>(0.2*$G$1),"error","")

Use similar formula for cells b2:f2.

Are you also trying to say you need a control that doesn't
allow the cell values to increase? If so, I've maybe
answered part of your question.

 

[JBoulton]

Marie,

I'd like to get the largest value in each cell that meets th
constraints. I have an evaluation line that indicates the % of th
total for each column, so that's not the problem.

My current formulas are =min(case1,case2,case3). I'd like to figur
out a way to modify that to include the final test (<.2) somehow.

I have a VBA solution, but it's not very elegant.

Ji

 

[hgrove]

JBoulton wrote...
...

The "almost final" answer to my analysis is contained in six cells,
say a1 - f1 with the sum of those in g1. The last step is to be
sure that none of the cells a1 - f1 exceeds 20% of the total in
g1. A1 - F1 can get smaller than they are now, but not bigger.

...

Depends on where you want this. If in any other cell, you could get
TRUE/FALSE indicator whether all values in A1:F1 meet the none over 20
of the sum of all criterion using the array formula

=AND(A1:F1<0.2*SUM(A1:F1)

 

[Dave R.]

I'd like to get the largest value in each cell that meets the
constraints. I have an evaluation line that indicates the % of the
total for each column, so that's not the problem.

Off to a bad start..a cell can hold one value.

My current formulas are =min(case1,case2,case3). I'd like to figure
out a way to modify that to include the final test (<.2) somehow.

Still not sure what you mean. If you want to return the MIN of some values
IF the values meet a certain condition then try
=MIN(IF(A1:A10<.2,A1:A10)) entered with ctrl shift enter.

I have a VBA solution, but it's not very elegant.

Posting your VBA solution might help someone else to figure out what you're
trying to do.

 

[JBoulton]

Dave,

Here's the code:
Row 101 contains the current % of Total
The actual cells in question are G101:L101
G92:L92 contain a holding place for values and M92 contains the sum o
G92:L92.

G101:L101 have the formula =MIN(case1, case2, case3,M92*.2)

Dim cell As Object
If Range("g101").Value <= 0.2 Then Range("g92") = Range("g92") * 10
This adjusts the starting place to above 20% if it's not alread
there.

For Each cell In Range("g101:l101")
While cell.Value > 0.2
Range("G80:L80").Select
Application.CutCopyMode = False
Selection.Copy
Range("G92").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Wend
Next
Application.CutCopyMode = False
Range("G92").Select
End Sub

So, basically I'm manually forcing the final result to meet the 20
requirement