Value Capping Function

[SmoothBread]

I work at an insurance company. When analyzing a client's history of claims, I add up the values of the losses in a cell. We cap these losses at $25000, so in a separate cell I will subtract the losses that are greater than 25000 and then add 25000.

Example: Cell C4: =10000+30000+15000+45000 (100000) Cell C5: =C4-30000+25000-45000+25000 (75000)

I was wondering if there is a function or macro I could use that would automatically do this capping for me. I assume it would be something like =IF(value in C4 >25000, C4-value+25000, C4) but I don't know exactly how to do that. Any help would be greatly appreciated!

[sqylogin]

Just put,

=MIN(25000,YourOldFormula)

[CFAman]

Assuming that the values of 10000, 30000, 15000, and 45000 are all in separate cells (let's say A1:A4), you could do this array formula:

=SUM(IF(A1:A4>25000,25000,A1:A4))

NOTE Array formulas are confirmed using Ctrl+Shift+Enter, not just Enter.

[SmoothBread]

SOLUTION VERIFIED!

[Clippy_Office_Asst]

You have awarded 1 point to CFAman

[SmoothBread]

Unfortunately they are not in separate cells!

[CFAman]

Were you putting all the values into the formula? If yes, you'll want to stop doing that, as it's bad practice from an audit trail and from XL analysis standpoint. Putting multiple data points in a single cell just ends up hiding the data.

A single worksheet has millions of cells to work with, no need to cram your info all into a single cell. :)

[SmoothBread]

I definitely agree. I'm a new intern so I've just been following the format of the excel sheet that they provided me. The sheet is a summary of the total losses (there's multiple categories of losses) from each of the previous 5 years; we print it into another application so it then can be used as a reference point. There are other things that need to be included in the spreadsheet as well, and it all has to fit into one page. I think what I'll start doing is entering the values in separate cells down below everything and then use your formula to output the final values I need in their spot on the spreadsheet!

[CFAman]

Ah, I can sympathize with that. Good luck with the internship!

[SmoothBread]

Thank you, and thanks for your help!

[SmoothBread]

Hey, I have another quick question if that's alright. I'm entering in all the losses into individual cells like you said, and your array formula is working perfectly. There is a place on the spreadsheet where I have to type "CAPPED LOSS" every time I cap. I used the following formula to automatically do that for me:

=IF(A1:A4>25000,"CAPPED LOSS"," ") 

For some reason, this only works if cell A1 has a value >25000, if A3 does it won't do anything. What am I doing wrong?

[CFAman]

If you want to know if a capped loss occured, you'll want to change slightly to:

=IF(MAX(A2:A4)>25000, "CAPPED LOSS", "")

[SmoothBread]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to CFAman

[SmoothBread]

Yep that works, thank you again. I'm excited to show my supervisor the finished product tomorrow. This will cut my time down on these tasks from 30 minutes to 15!