interrelated constraints

  • Thread starter Thread starter JBoulton
  • Start date Start date
J

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
 
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
 
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.
 
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
 
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.
Click to expand...
...

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)
 
JBoulton > said:
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

Posting your VBA solution might help someone else to figure out what you're
trying to do.
 
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
 
Back
Top