Setting a max/min value to use in a VBA function

[carrotik]

Hi,

I have a set of four raw values that I want to put in a calculation - Debt/Ebitda, OAM/TA, OpProfit and QuickRatio. Now, each value must fall between the cap (max possible value) and the floor (min possible value) for that combination of value (four types in total, as above) and industry (ten types in total). Say OpProfit for Agriculture has a range from 0 to 0.4, so if I have a ratio of 0.5 I want to use 0.4 in my calculations. I have to do a VLOOKUP-esque VBA function for this currently.

Also for Debt/Ebitda it's slightly different - if the raw value falls below the floor, the cap value is used.

Is there a way of capping / flooring my raw values using VBA functions before I put them in my equation? I know you can do it using a series of IF() worksheet functions but I'm already running the calculations using a VBA function.

Thanks loads

[Leith Ross]

Hello carrotik,

Welcome to the forum! It would help to see how your data is arranged on the worksheet. Could you either post your workbook (zip it first) or provide a example of cell values and locations in your post. I understand what you want to do, and in order to write a macro I need to know where the data are.

Thanks,
Leith Ross