CEILING FUNCTION IN EXCEL?

[ottograham]

If you have a dollar amount which needs to be capped at $5,000, and then determine the amount excess of the $5,000, what formula would you use?

I've tried ceiling with no success.

E.g. $184 capped at $5,000 would leave 184 and 0 excess

$72,400 capped at $5,000 would result in $5,000 primary with $62,400 excess

 

[pjm]

Hiya

If your amount is in cell a1 then:

=min(5000,a1) will give you the "primary" amount, and

=max(0,min(a1-5000)) will give you the "excess" amount.

HTH
pjm

 

[dsi]

You could use a simple If statment. If your values are in column A and you want a single result in column B. If you want both the primary and excess amounts, use pjm's method.

=IF(A1>5000,A1-5000,A1)

 

[ottograham]

Both were helpful. Thank you!

 

[GeekGirlau]

Quick tip - usually it's not a great idea to have values entered directly into the formula. Rather than having 5000 in the formula itself, type 5000 into a cell and have your formula refer to that cell. Makes it easier to work with if your ceiling changes.

 

[ottograham]

Hey GeekGirlau:

I appreciate your comment. It brings up the next step in the process. I'd like to start filling cells with values based upon some other value (a dlookup in Access).

For example, I have a column "CODE" and and column "LOSS RATE". I'd like to enter the code and have the rate filled in by looking it up in some other worksheet.

Does Excel have a built in function to do this.

Thanks

 

[pjm]

Hi again!

The vlookup() function is what you need. Full details are in the Excel Help. Post again if you need further info.

Regards,
pjm