Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |