Conditionally Add Values to Cells in a Table

[mcongdon]

Hi All,
I have a table in excel that has Y-axis of Share Price, X-axis of Price per Share, and the data in the middle is the associated charge. I want to be able to add a certain amount to cells that have a principal amount between certain ranges.

For Example:

            Price     
         $15      $20
S  10    2.50    2.75
h  11    2.61    2.80
a  12    2.96    2.93
r  13    3.42    3.15
e  14    3.62    3.50
s  15    4.05    3.75
 
I want to add a commission of $30.00 to every range with a principal amount (Price*Shares) over $190.  The resulting table would look like this:

             Price     
          $15      $20
S  10     2.50    32.75
h  11     2.61    32.80
a  12     2.96    32.93
r  13    33.42    33.15
e  14    33.62    33.50
s  15    34.05    33.75

The issue is that I have to be able to set it up in excel using a macro or VBA code of some sort. The end user has to be able to specify a certain range of principal amounts he wants to add a certain charge to, then click a button and have it update the grid. I think I'll have to have 2 separate grids, one with the original value, and one with the commission added on top. I have no problem coming up with a macro to reset the one with the added commission, I can do that no problem. I just can't figure out how to add the commission the user specifies in certain ranges.

Thanks!

 

[SkipVought]

Hi,

I can't make any sense of exactly what you are doing.

Be I do think that I may understand generally what you need to do.

You don't need a macro. It can all be done with formulas.

Your user can specify whatever values. If they are a fixed set of values, you can use Data > Validation -- LIST

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Rofeu]

Hi,

I'd reserve a region in your workbook where the user can specify the ranges and the markup, i.e.

From To Markup
2.50 5.00 30
5.00 7.50 35
etc...

Then use maybe a VLOOKUP formulae with the last parameter set to true if your ranges are continous. Otherwise some variation on SUMPRODUCT to get the markup and just add it to the amount.

If those amounts you have in ypur example are already formulae you can just add these to the cells, otherwise you'll need another table.

Cheers,

Rofeu

 

[mcongdon]

Hey guys,
I'm trying to think of a way to explain this that isn't so confusing. I need to take the base commission from one table and add a markup to this commission based on the principal amount of the trade. VLOOKUP, from what I know, is based on the first column of data and HLOOKUP is based on the first row of data. I need to use the product of the row and column to lookup by (principal).

The second issue is that I need to be able to add commissions to different ranges. For example in cells that have a principal of $500-$1000, I want to add $30. In ranges of $1200-$1500, I don't want to add anything. In ranges of $1500-$2000, I want to add $50. These are all numbers that have to be able to change as well. I have to be able to change the ranges and the markup.

Hope this is somewhat clearer...

 

[SkipVought]

VLOOKUP, from what I know, is based on the first column of data and HLOOKUP is based on the first row of data.

Sure, that should work for you. Post actual examples (copy 'n' paste) from your sheet to illustrate if you're having problems.

For example in cells that have a principal of $500-$1000, I want to add $30. In ranges of $1200-$1500, I don't want to add anything. In ranges of $1500-$2000, I want to add $50.

Build a lookup table like this...
[tt]
Amt AddVal
500 30
1200 0
1500 50
[/tt]
ausn use vlookup with a TRUE last argument.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}