software

How do I correct to make conditonal formula with number values ?

My main worksheet has at leat 24 columns and as many as 30 rows, all have
numbered values. Rows are defined names.

I want to get average values when at least 2 conditions have certain values.

I tried =SUM(IF(AND(MapKpa:MapKpa=40),(RPM:RPM=1000),grmcy c:grmcyc))
and answer given is really total of all 30,000 row values for grmcyc and not
when mapkpa values = 40 and RPM = 1000.

Also best would be if the conditions allow lets say when Mapkpa is between
40 and 45 and RPMs between 1000 and 1500 to have a window of grmcyc average

I used Sum but really want an average of grmcyc when Mapkpa and RPM meet the
conditions so I can build a results table of what the grmcyc average value
was from 20 to 105 KPA in 5 KPA windows along with smaller RPM ranges.

Thanks,
Perhaps something along these lines would be of some help ..

A sample construct is available at:
www.savefile.com/files/8103514
Conditional_Averaging_TeamZR-1_wks.xls

Assuming source data is within A2:C11,
and we have the defined ranges:
MapKpa =Sheet1!$A$2:$A$11
RPM =Sheet1!$B$2:$B$11
grmcyc =Sheet1!$C$2:$C$11

With the lower amp; upper limits for MapKpa amp; RPM
specified in say, F2:F3 and G2:G3 respectively,

Put in F4, array-enter the formula
(i.e. press CTRL SHIFT ENTER):
=AVERAGE(IF((MapKpagt;=F2)*(MapKpalt;=G2)*(RPMgt;=F3)*(R PMlt;=G3),grmcyc))

F4 will return the required quot;Average grmcycquot;
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Team ZR-1quot; gt; wrote in message
...
gt; How do I correct to make conditonal formula with number values ?
gt;
gt; My main worksheet has at leat 24 columns and as many as 30 rows, all have
gt; numbered values. Rows are defined names.
gt;
gt; I want to get average values when at least 2 conditions have certain
values.
gt;
gt; I tried =SUM(IF(AND(MapKpa:MapKpa=40),(RPM:RPM=1000),grmcy c:grmcyc))
gt; and answer given is really total of all 30,000 row values for grmcyc and
not
gt; when mapkpa values = 40 and RPM = 1000.
gt;
gt; Also best would be if the conditions allow lets say when Mapkpa is between
gt; 40 and 45 and RPMs between 1000 and 1500 to have a window of grmcyc
average
gt;
gt; I used Sum but really want an average of grmcyc when Mapkpa and RPM meet
the
gt; conditions so I can build a results table of what the grmcyc average value
gt; was from 20 to 105 KPA in 5 KPA windows along with smaller RPM ranges.
gt;
gt; Thanks,
gt;
gt;
Max thank-you for the reply !

My end results need to be a table so it would have like vertical at top
being RPMs like
and MAPkpa horziontal matrix cells

1,0000 2,000 3,000 etc to =gt; 7,0000
MAP
25
30
35

Think we can try building a 2 variable Data gt; Table for:

...................RPM ..............
gt; X 1,000 2,000 3,000 etc to =gt; 7,000
gt; 25
gt; 30
gt; 35
(MAP)

which can calc amp; populate the entire table,
but we'd need to link the top left cell (marked quot;Xquot; above)
to the formula for quot;grmcyc averagequot;
which is calculated from the MAP and the RPM,

For e.g.: if you have say,
the formula in C1 for quot;grmcyc averagequot;:
= 0.5*A1 0.02*B1
where A1 = MAP value, B1 = RPM value
then we could put in quot;Xquot;: =C1, and proceed from there
with the Data gt; Table

Your actual formula in C1 could of course, be much more complex than the
simple one above, but as long as it depends on both MAP and RPM, then the
Data gt; Table functionality can be used.

Could you post the actual formula for quot;grmcyc averagequot;,
and the cell it's in ?
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Team ZR-1quot; gt; wrote in message
...
gt; Max thank-you for the reply !
gt;
gt; My end results need to be a table so it would have like vertical at top
gt; being RPMs like
gt; and MAPkpa horziontal matrix cells
gt;
gt; 1,0000 2,000 3,000 etc to =gt; 7,0000
gt; MAP
gt; 25
gt; 30
gt; 35
gt; .
gt; etc to 105
gt;
gt; Thus each cell would be the grmcyc average of those conditions.
gt;
gt; Reason I used the MapKpa:Mapkpa ie. defined named rows was so that excel
gt; would on its own look at each row and see the data and make decisons from
gt; that where in your case you set limits on in other cells.
gt;
gt; How can I do this for that is a lot of conditions, ie, when Map and RPM =
25
gt; / 1000
gt; Map and RPM = 25 / 2000, etc all the way to map = 105 / 7,000 ?
Here's a revised sample construct (to suit actual layout):
cjoint.com/?breycwYwIH
TeamZR1_veformula_2.xls

gt; ... please look at the #NUM error in VE sheet
gt; which gets values from data sheet and
gt; then makes table from VE table.
gt; What is needed to correct this ?

The earlier formula is an array formula which cannot accept entire col
references. Your 3 defined ranges: grmcyc, MapKpa, RPM were pointing to
entire col references (eg: =Data!$C:$C).

Let's redefine the 3 ranges in the formula to be dynamic ranges:

grmcyc =OFFSET(Data!$M$2,,,COUNTA(Data!$M:$M)-1)
MapKpa =OFFSET(Data!$N$2,,,COUNTA(Data!$N:$N)-1)
RPM =OFFSET(Data!$C$2,,,COUNTA(Data!$C:$C)-1)

Then in Sheet: VE,
we'll also revise the table's format to better suit, viz.:

Have the RPM running across in pairs (the lower amp; upper limits)
in C2:C3, B2:B3, D23 ... viz:

RPM
400.0 800.0 1200.0
800.0 1200.0 1600.0 etc

and the MapKpa figures (again in pairs)
running down in A4:B4, A5:B5, A6:B6 ... , viz:

MapKpa
15.0 20.0
20.0 25.0
25.0 30.0
30.0 35.0
etc

Then put in the starting cell C4, and array-enter
(press CTRL SHIFT ENTER):

=IF(ISERROR(AVERAGE(IF((MapKpagt;=$A4)*(MapKpalt;$B4)* (RPMgt;=C$2)*(RPMlt;C$3),grmcy
c))),quot;---quot;,AVERAGE(IF((MapKpagt;=$A4)*(MapKpalt;$B4)*(RPMgt;=C$2) *(RPMlt;C$3),grmcyc
)))

and copy C4 across and down to populate the table

The revised formula above is basically the same as before, except re-set to
point correctly at the limits for RPM amp; MapKpa (with no overlaps), and with
an error-trap added to return a neater: quot;---quot; instead of quot;uglyquot; error msgs.
It should work ok now.

P/s: Please keep discussions within the newsgroup
thread for the benefit of all.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Max thanks again for the help

2 questions, being in real world there can be up to 30,000 rows when I added
formula just for the 1st cell ( had 15,000 rows of data) the laptop which is
a 1 Ghz CPU Sony with 128 meg RAM free with MS 2000 OS as soon as I hit sht
ctl enter the excel was in calculate and CPU went to 100 % in use and was
like that for 15 minutes ! leaving no CPU for the laptop.
Is there some option setting in Excel to prevent this from taking so long to
calculate one cell ?

Also what easy way is there to copy the 1st cell's formula to properly copy
to all other cells in the table and properly change the varables ?

Thanks,

quot;Maxquot; wrote:

gt; Here's a revised sample construct (to suit actual layout):
gt; cjoint.com/?breycwYwIH
gt; TeamZR1_veformula_2.xls
gt;
gt; gt; ... please look at the #NUM error in VE sheet
gt; gt; which gets values from data sheet and
gt; gt; then makes table from VE table.
gt; gt; What is needed to correct this ?
gt;
gt; The earlier formula is an array formula which cannot accept entire col
gt; references. Your 3 defined ranges: grmcyc, MapKpa, RPM were pointing to
gt; entire col references (eg: =Data!$C:$C).
gt;
gt; Let's redefine the 3 ranges in the formula to be dynamic ranges:
gt;
gt; grmcyc =OFFSET(Data!$M$2,,,COUNTA(Data!$M:$M)-1)
gt; MapKpa =OFFSET(Data!$N$2,,,COUNTA(Data!$N:$N)-1)
gt; RPM =OFFSET(Data!$C$2,,,COUNTA(Data!$C:$C)-1)
gt;
gt; Then in Sheet: VE,
gt; we'll also revise the table's format to better suit, viz.:
gt;
gt; Have the RPM running across in pairs (the lower amp; upper limits)
gt; in C2:C3, B2:B3, D23 ... viz:
gt;
gt; RPM
gt; 400.0 800.0 1200.0
gt; 800.0 1200.0 1600.0 etc
gt;
gt; and the MapKpa figures (again in pairs)
gt; running down in A4:B4, A5:B5, A6:B6 ... , viz:
gt;
gt; MapKpa
gt; 15.0 20.0
gt; 20.0 25.0
gt; 25.0 30.0
gt; 30.0 35.0
gt; etc
gt;
gt; Then put in the starting cell C4, and array-enter
gt; (press CTRL SHIFT ENTER):
gt;
gt; =IF(ISERROR(AVERAGE(IF((MapKpagt;=$A4)*(MapKpalt;$B4)* (RPMgt;=C$2)*(RPMlt;C$3),grmcy
gt; c))),quot;---quot;,AVERAGE(IF((MapKpagt;=$A4)*(MapKpalt;$B4)*(RPMgt;=C$2) *(RPMlt;C$3),grmcyc
gt; )))
gt;
gt; and copy C4 across and down to populate the table
gt;
gt; The revised formula above is basically the same as before, except re-set to
gt; point correctly at the limits for RPM amp; MapKpa (with no overlaps), and with
gt; an error-trap added to return a neater: quot;---quot; instead of quot;uglyquot; error msgs.
gt; It should work ok now.
gt;
gt; P/s: Please keep discussions within the newsgroup
gt; thread for the benefit of all.
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt;
gt;
gt;

quot;Team ZR-1quot; wrote:
gt; Max thanks again for the help

You're welcome !

gt; 2 questions, being in real world there can
gt; be up to 30,000 rows when I added
gt; formula just for the 1st cell ( had 15,000 rows of data)
gt; the laptop which is a 1 Ghz CPU Sony with
gt; 128 meg RAM free with MS 2000 OS as soon as I hit sht
gt; ctl enter the excel was in calculate and CPU
gt; went to 100 % in use and was like that for 15 minutes !
gt; leaving no CPU for the laptop.

gt; Is there some option setting in Excel
gt; to prevent this from taking so long to calculate one cell ?

For calculation intensive applications,
I'd set the calc mode to Manual

Click Tools gt; Options gt; Calculation tab
Check: Manual gt; OK

This setting will hold quot;unnecessquot; calc until calc is required
When calc/recalc is required, press F9

Another thing we could do is to simplify the last formula.
For eg: remove the error-trap, and just make do
with the quot;plainquot; array formula in A6:
=AVERAGE(IF((MapKpagt;=$A4)*(MapKpalt;$B4)*(RPMgt;=C$2)* (RPMlt;C$3),grmcyc))

This should speed things up a bit,
but the quot;uglyquot; error returns would be visible

To hide these w/o using the error-trap in the formula,
we could conditionally format the all formula cells
to mask those with error returns by
choosing a font color to match the fill color

For eg: in the sample file's sheet VE,
the array formulas are in C4:I21

Select C4:I21
Then click Format gt; Cond Formatting
Formula is: =ISERROR(C4)
Click Format button gt; Font tab gt; choose light blue* font color gt; OK out (as
the fill color is light blue)

Another thing we could do is dispense with the use of the 3 dynamic ranges
in the earlier set-up.
(These dynamic ranges use OFFSET which is volatile)

Use 3 fixed* defined ranges instead, eg:
(*but not entire col refs in this instance, as explained earlier)

grmcyc: =Data!$M$2:$M$1000 (say)
MapKpa: =Data!$N$2:$N$1000
RPM: =Data!$C$2:$C$1000

Note that the 3 defined ranges should be identically structured, and use the
smallest range possible. The quot;1000quot; illustrated above may be excessive.

You could tinker with applying the above alternatives in the earlier sample
provided, get these working ok first, then apply them to your actual file.
The calc performance should improve, but by what extent, I don't know.

gt; Also what easy way is there to copy the
gt; 1st cell's formula to properly copy
gt; to all other cells in the table and
gt; properly change the variables ?

Not sure what you mean by quot;properly copyquot;. I'd usually fill formulas by
dragging the fill handle (i.e. the black quot;boxquot; at the bottom right-corner of
the anchor cell - eg: C4) across / down after entering the formula there.

Besides presentation value (quot;easy-to-readquot;), designing the table layout is
also important, especially where the anchor cell's formula needs to change
relatively and point to the correct values in both the top row(s) / left
col(s) as we copy it across and down to populate (Thought the table design
proposed earlier in the sample was quite decent, no? lt;ggt;)

Using a dollar sign in the cell ref ($) will fix the col / row ref. Eg: $A4
means we're fixing the point to col A: quot;Aquot; as we copy across, but we want
the row ref (quot;4quot;) to change when we copy down. Conversely for A$4. And if
we fix both col / row ref ($A$4), it means we don't want the formula's point
to this cell: A4 to change whether we're copying across or down. Conversely
for a cell w/o any dollar signs: A4.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
gt; For eg: remove the error-trap, and just make do
gt; with the quot;plainquot; array formula in A6:

Typo: A6 should read as C4
in the part above
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
gt; Select C4:I21

should have read as:
gt; Select C4:I21 (with C4 active)

The active cell in the selection C4:I21 must be C4,
(and not one of the 3 corners of the range C4:I21)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
gt; (and not one of the 3 corners of the range C4:I21)

should be:
gt; (and not one of the other 3 corners of the range C4:I21)

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---