"Min" Function in Excel

rodwilma

New Member
Joined
Nov 12, 2012
Messages
11
In the below excel table I have two steps and am stuck on the second. My first, StepA, Apply attachment point,Total Value – Attachment Point. If result is less than zero, than the exposure to the policy = 0. (= IF(TotalVal – AttchPt < 0, 0, TotalVal – AttchPt))

For Step B - Apply Policy Limit. Compare result from Step A to the Policy Limit, take the minimum of the two.
Note – I must take into account policies where the Policy Limit = 0. In these cases the coverage should be treated as unlimited. In cases where the LIMIT = 0, an “effective limit” equal to the total value of the specific ZIP Code should be assumed.


ACCGRPNUM
POLICYNUM
AttchPT
POLICY LIMIT
STATE
ZIP
TotalVal
StepA
Step B
1
1
$10,000,000
$2,500,000

20171
$66,296,000
$56,296,000
1
1
$10,000,000
$2,500,000

30245
$18,556,000
$8,556,000
1
1
$10,000,000
$2,500,000

30327
$26,602,000
$16,602,000
10027
10016
$0
$0
NY
11102
$11,000
$11,000
10028
10017
$0
$0
NY
11105
$429,000
$429,000
1
1
$10,000,000
$2,500,000

32024
$266,000
$0

<tbody>
</tbody>


Would I code using the 'min' function? I'm stuck on how to apply $0 if listed in policy limit.

Any input would be so helpful.

Thanks!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
you can add an IF() function like this

=IF(StepA=0, TotalVal,min(TotalVal, StepA))
 
Upvote 0
<c2,0,g2-c2)
<c2,0,g2-c2)< font="">

<tbody>
</tbody>
</c2,0,g2-c2)<></c2,0,g2-c2)

??? cannot delete !!
 
Last edited:
Upvote 0
Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
1
ACCG
RPNUM
POLICY
NUM
AttchPTPOLICY LIMITSTATEZIPTotalValStepAStep BFormulaOne StepFormula
2
1​
1​
10,000,000​
2,500,000
20171​
66,296,000
56,296,000
2,500,000​
=IF(D2=0,G2,MIN(H2,D2))
2500000​
=(D2<>0)*MIN(H2,D2)+(D2=0)*G2
3
1​
1​
10,000,000​
2,500,000​
30245​
18,556,000​
8,556,000​
2,500,000​
2500000​
4
1​
1​
10,000,000​
2,500,000​
30327​
26,602,000​
16,602,000​
2,500,000​
2500000​
5
10027​
10016​
0​
0​
NY
11102​
11,000​
11,000​
11,000​
11000​
6
10028​
10017​
0​
0​
NY
11105​
429,000​
429,000​
429,000​
429000​
7
1​
1​
10,000,000​
2,500,000​
32024​
266,000​
0​
0​
0​
Sheet: Sheet3
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top