Array Formula Needed?

  • Thread starter Thread starter Dan M. Marr
  • Start date Start date
D

Dan M. Marr

I'm using Excel 2002 with Windows XP.
I have a formula in cells A1 and B1 both of which return
numerical values.

I have a table at C1:D15. Cells C1:C15 contain decimal
values which represent the orifice size of a nozzle.
Cells D1:D15 are the coorespond part numbers for those
nozzles.

I need to know the fewest number of nozzles, based on the
nozzles orifice size (cells C1:C15), it would take to
exceed 35% of the value in A1 or B1, which ever value is
smaller.

I also need to know the fewest number of nozzles, again
based on the nozzle orifice size (cells C1:C15), it would
take to exceed 85% of the value in A1 or B1, which ever
value is smaller.

Determining which cell, between A1 and B1 is smaller is a
simple IF statement:
={IF(A1<B1,"EXE THIS CODE","OTHERWISE THIS CODE")}

I'm thinking it will take an ARRAY formula and I've
created them before (i.e. CTRL+SHIFT+ENTER), but I'm not
sure how to put it all together and test for the lowest
whole number (fewest number of nozzles).

Any Ideas?
 
One way, if I understand you correctly. In E1:E15 array-enter:

=CEILING((MIN(A$1,B$1)*35%+0.000001)/C1:C15,1)

the +0.000001 is used to make the result exceed 35%, rather than
just meet it. Scale it appropriately for your nozzle sizes.
 
Back
Top