formula help

[Chris]

I am trying to create a formula in a spread sheet, but I can't seem to figure
it out. I don't even know if its possible but I want to return a value in
each cell c based on the following paramaters for A & B. Is there anyone who
can help point me in the right direction? I would be eternally grateful.
a b c

 

[Ron Rosenfeld]

I am trying to create a formula in a spread sheet, but I can't seem to figure
it out. I don't even know if its possible but I want to return a value in
each cell c based on the following paramaters for A & B. Is there anyone who
can help point me in the right direction? I would be eternally grateful.
a b c

The example and description you give are not exclusive. What are the rules if
a pair of parameters fit into more than one row?


--ron

 

[Chris]

I should have been more specific. I am trying to create a sheet to track
sales totals and margins based on a point system. I would want it to assign
the highest number from column C that applies. So it would probably have to
go backwards. eg. if not 2000 @ 40, then 2000 @ 35, then 2000 @ 30 ect.

 

[Bernd Plumhoff]

Enter following values/formula into cells A1:C8, for
example:
2001 0.31 =MAX((A1>A2:A8)*(B1>B2:B8)*C2:C8)
500 0.5 1
750 0.5 2
1000 0.35 2
1000 0.5 4
2000 0.3 2
2000 0.35 3
2000 0.4 5

Formula in C1 has to be entered as array formula (hit
CTRL+SHIFT+ENTER, not only ENTER).

Just a hint: Maybe you want to use >= instead of > ?!

HTH,
Bernd

 

[Ron Rosenfeld]

I should have been more specific. I am trying to create a sheet to track
sales totals and margins based on a point system. I would want it to assign
the highest number from column C that applies. So it would probably have to
go backwards. eg. if not 2000 @ 40, then 2000 @ 35, then 2000 @ 30 ect.

OK, I think I understand.

If you label your three columns of data: a, b and points, then the following
*array-entered* formula should do what you describe:

=MAX((Sales>a)*(Margin>b)*points)

To *array-enter* a formula, after typing/pasting it into a cell, instead of
just hitting <enter>, hold down <ctrl><shift> while hitting <enter>. XL will
place braces {...} around the formula.

One caveat: In your original post you used symbology implying that Sales and
Margin had to be GREATER than certain values. That means that if you had Sales
of exactly 500, and Margin of exactly 50%, the result of the above formula will
be ZERO, not ONE. If that is not what you wish, you may need to change some
values, or change the comparison operators in the formula from ">" (Greater
than) to ">=" (Greater than or equal to).


--ron

 

[Chris]

I'm not sure this is working right. What I want to do is track orders
entered per sales person, and award a cetain number of points to each
quailifiying order which will then be added up at the end of each month. the
points will be used for a bonus program. there are actually 2 more levels
5000, & 10000 with 3 different margin levels for each of those also, but I
left that off in the interest of space.
I was hoping excel could compare the 2 pieces of data entered and return a
single number in the c column which could then be added up. Unfortunately I
am not an expert in Excel.