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:
http://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 & upper limits for MapKpa & 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((MapKpa=F2)*(MapKpa<=G2)*(RPM=F3)*(R PM<=G3),grmcyc))

F4 will return the required "Average grmcyc"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Team ZR-1" wrote in message
...
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,

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 = 7,0000
MAP
25
30
35

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

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

which can calc & populate the entire table,
but we'd need to link the top left cell (marked "X" above)
to the formula for "grmcyc average"
which is calculated from the MAP and the RPM,

For e.g.: if you have say,
the formula in C1 for "grmcyc average":
= 0.5*A1 + 0.02*B1
where A1 = MAP value, B1 = RPM value
then we could put in "X": =C1, and proceed from there
with the Data 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 Table functionality can be used.

Could you post the actual formula for "grmcyc average",
and the cell it's in ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Team ZR-1" wrote in message
...
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 = 7,0000
MAP
25
30
35
.
etc to 105

Thus each cell would be the grmcyc average of those conditions.

Reason I used the MapKpa:Mapkpa ie. defined named rows was so that excel
would on its own look at each row and see the data and make decisons from
that where in your case you set limits on in other cells.

How can I do this for that is a lot of conditions, ie, when Map and RPM =
25
/ 1000
Map and RPM = 25 / 2000, etc all the way to map = 105 / 7,000 ?

Here's a revised sample construct (to suit actual layout):
http://cjoint.com/?breycwYwIH
TeamZR1_veformula_2.xls

... please look at the #NUM error in VE sheet
which gets values from data sheet and
then makes table from VE table.
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 & upper limits)
in C2:C3, B2:B3, D2:D3 ... 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((MapKpa=$A4)*(MapKpa<$B4)* (RPM=C$2)*(RPM<C$3),grmcy
c))),"---",AVERAGE(IF((MapKpa=$A4)*(MapKpa<$B4)*(RPM=C$2) *(RPM<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 & MapKpa (with no overlaps), and with
an error-trap added to return a neater: "---" instead of "ugly" error msgs.
It should work ok now.

P/s: Please keep discussions within the newsgroup
thread for the benefit of all.
--
Max
Singapore
http://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,

"Max" wrote:

Here's a revised sample construct (to suit actual layout):
http://cjoint.com/?breycwYwIH
TeamZR1_veformula_2.xls

... please look at the #NUM error in VE sheet
which gets values from data sheet and
then makes table from VE table.
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 & upper limits)
in C2:C3, B2:B3, D2:D3 ... 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((MapKpa=$A4)*(MapKpa<$B4)* (RPM=C$2)*(RPM<C$3),grmcy
c))),"---",AVERAGE(IF((MapKpa=$A4)*(MapKpa<$B4)*(RPM=C$2) *(RPM<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 & MapKpa (with no overlaps), and with
an error-trap added to return a neater: "---" instead of "ugly" error msgs.
It should work ok now.

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

"Team ZR-1" wrote:
Max thanks again for the help

You're welcome !

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 ?

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

Click Tools Options Calculation tab
Check: Manual OK

This setting will hold "unnecess" 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 "plain" array formula in A6:
=AVERAGE(IF((MapKpa=$A4)*(MapKpa<$B4)*(RPM=C$2)* (RPM<C$3),grmcyc))

This should speed things up a bit,
but the "ugly" 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 Cond Formatting
Formula is: =ISERROR(C4)
Click Format button Font tab choose light blue* font color 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 "1000" 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.

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 variables ?

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

Besides presentation value ("easy-to-read"), 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? <g)

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: "A" as we copy across, but we want
the row ref ("4") 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
http://savefile.com/projects/236895
xdemechanik
---

For eg: remove the error-trap, and just make do
with the "plain" array formula in A6:

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

Select C4:I21

should have read as:
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
http://savefile.com/projects/236895
xdemechanik
---

(and not one of the 3 corners of the range C4:I21)

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

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

I got rid of the error trap from all formula's for that table since it has
over 40 cells and that did help some on the 100% CPU for long periods of time
and also set for manual calculations since even with error trap gone it still
takes some time to calc this workbook which has 21 pages ( mostly graphs)

I also did do the conditional format since any cells that had no data ended
up with a #DIVD0 error.

Can anyone answer I am using 2002 Excel, is 2003 version any faster and
function better with large workbooks ?

Thanks again for all the hekp

JR

... takes some time to calc this workbook
which has 21 pages ( mostly graphs)

For calc-intensive books, I'd usually do plain copy paste of the source
table(s) into a new book and plot all the charts / graphs there. From my
experience, having separate books for charting purposes does help somewhat
to keep file sizes sane and calc performance tolerable. Of course, if you
want to keep the charts dynamic to the formula-evaluated source tables, this
approach may not be feasible.

(I'd usually avoid linking between books as well)

.. I am using 2002 Excel, is 2003 version any faster and
function better with large workbooks ?

I'm still using only Excel 97 to-date <g,
so I won't know about the higher versions

Hang around awhile for possible views from others
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Team ZR-1" wrote in message
...
I got rid of the error trap from all formula's for that table since it has
over 40 cells and that did help some on the 100% CPU for long periods of
time
and also set for manual calculations since even with error trap gone it
still
takes some time to calc this workbook which has 21 pages ( mostly graphs)

I also did do the conditional format since any cells that had no data
ended
up with a #DIVD0 error.

Can anyone answer I am using 2002 Excel, is 2003 version any faster and
function better with large workbooks ?

Thanks again for all the hekp

JR

Oops, slight clarification

... I'd usually do plain copy paste of the source

"plain copy paste" reads clearer as:
copy paste special values / formats
(pasting a "plain" copy of the source elsewhere, w/o formulas)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---