#1   Report Post  
Chris
 
Posts: n/a
Default formula help

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
500 50% 1
750 50% 2
1000 35% 2
1000 50% 4
2000 30% 2
2000 35% 3
2000 40% 5

  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 10 Feb 2005 07:15:04 -0800, "Chris"
wrote:

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
500 50% 1
750 50% 2
1000 35% 2
1000 50% 4
2000 30% 2
2000 35% 3
2000 40% 5


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
  #3   Report Post  
Chris
 
Posts: n/a
Default

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.

"Ron Rosenfeld" wrote:

On Thu, 10 Feb 2005 07:15:04 -0800, "Chris"
wrote:

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
500 50% 1
750 50% 2
1000 35% 2
1000 50% 4
2000 30% 2
2000 35% 3
2000 40% 5


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

  #4   Report Post  
Bernd Plumhoff
 
Posts: n/a
Default

Enter following values/formula into cells A1:C8, for
example:
2001 0.31 =MAX((A1A2:A8)*(B1B2: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
  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 10 Feb 2005 13:37:04 -0800, "Chris"
wrote:

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((Salesa)*(Marginb)*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


  #6   Report Post  
Chris
 
Posts: n/a
Default

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.

"Bernd Plumhoff" wrote:

Enter following values/formula into cells A1:C8, for
example:
2001 0.31 =MAX((A1A2:A8)*(B1B2: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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 02:49 PM
Relative Indirect Formula Referencing? Damian Excel Worksheet Functions 1 January 7th 05 05:16 AM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 02:27 AM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM


All times are GMT +1. The time now is 10:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"