"Min" Function in Excel

R

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.


[TABLE="width: 753"]
<tbody>[TR]
[TD]ACCGRPNUM
[/TD]
[TD]POLICYNUM
[/TD]
[TD]AttchPT
[/TD]
[TD]POLICY LIMIT
[/TD]
[TD]STATE
[/TD]
[TD]ZIP
[/TD]
[TD]TotalVal
[/TD]
[TD]StepA
[/TD]
[TD]Step B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]$10,000,000
[/TD]
[TD]$2,500,000
[/TD]
[TD]
[/TD]
[TD]20171
[/TD]
[TD]$66,296,000
[/TD]
[TD]$56,296,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]$10,000,000
[/TD]
[TD]$2,500,000
[/TD]
[TD]
[/TD]
[TD]30245
[/TD]
[TD]$18,556,000
[/TD]
[TD]$8,556,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]$10,000,000
[/TD]
[TD]$2,500,000
[/TD]
[TD]
[/TD]
[TD]30327
[/TD]
[TD]$26,602,000
[/TD]
[TD]$16,602,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10027
[/TD]
[TD]10016
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]NY
[/TD]
[TD]11102
[/TD]
[TD]$11,000
[/TD]
[TD]$11,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10028
[/TD]
[TD]10017
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]NY
[/TD]
[TD]11105
[/TD]
[TD]$429,000
[/TD]
[TD]$429,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]$10,000,000
[/TD]
[TD]$2,500,000
[/TD]
[TD]
[/TD]
[TD]32024
[/TD]
[TD]$266,000
[/TD]
[TD]$0
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
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="">[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
</c2,0,g2-c2)<></c2,0,g2-c2)

??? cannot delete !!
 
Last edited:
Upvote 0
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][th]
L​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]ACCG
RPNUM[/td][td]POLICY
NUM[/td][td]AttchPT[/td][td]POLICY LIMIT[/td][td]STATE[/td][td]ZIP[/td][td]TotalVal[/td][td]StepA[/td][td]Step B[/td][td]Formula[/td][td]One Step[/td][td]Formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
1​
[/td][td]
1​
[/td][td]
10,000,000​
[/td][td]
2,500,000​
[/td][td][/td][td]
20171​
[/td][td]
66,296,000​
[/td][td]
56,296,000​
[/td][td]
2,500,000​
[/td][td] =IF(D2=0,G2,MIN(H2,D2))[/td][td]
2500000​
[/td][td] =(D2<>0)*MIN(H2,D2)+(D2=0)*G2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
1​
[/td][td]
1​
[/td][td]
10,000,000​
[/td][td]
2,500,000​
[/td][td][/td][td]
30245​
[/td][td]
18,556,000​
[/td][td]
8,556,000​
[/td][td]
2,500,000​
[/td][td][/td][td]
2500000​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
1​
[/td][td]
1​
[/td][td]
10,000,000​
[/td][td]
2,500,000​
[/td][td][/td][td]
30327​
[/td][td]
26,602,000​
[/td][td]
16,602,000​
[/td][td]
2,500,000​
[/td][td][/td][td]
2500000​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
10027​
[/td][td]
10016​
[/td][td]
0​
[/td][td]
0​
[/td][td]NY[/td][td]
11102​
[/td][td]
11,000​
[/td][td]
11,000​
[/td][td]
11,000​
[/td][td][/td][td]
11000​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
10028​
[/td][td]
10017​
[/td][td]
0​
[/td][td]
0​
[/td][td]NY[/td][td]
11105​
[/td][td]
429,000​
[/td][td]
429,000​
[/td][td]
429,000​
[/td][td][/td][td]
429000​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
1​
[/td][td]
1​
[/td][td]
10,000,000​
[/td][td]
2,500,000​
[/td][td][/td][td]
32024​
[/td][td]
266,000​
[/td][td]
0​
[/td][td]
0​
[/td][td][/td][td]
0​
[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

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