cmartin311
New Member
- Joined
- Dec 20, 2018
- Messages
- 3
Have a problem getting the formula down. I've tried =MIN(January!C20*0.25*0.15) my salary field my % field =MIN(0.15,(January!E3+February!E3+March!E3)/3)
The salaries vary. So let's say on a 75,000 dollar salary, the quarterly bonus can pay out no more than 15% or no more than 2,812.50. But if you have a salary of 83,000 the pay out can be no more than 3075.00 Monthly bonus amt earned is below. In option 1 below the quarterly total is more than the allowable amount, option 2 is less.
[TABLE="width: 174"]
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64" style="width: 48pt;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody>[TR]
[TD="width: 99, bgcolor: transparent"]Bonus Amount earned
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]$937.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Feb[/TD]
[TD="bgcolor: transparent, align: right"]$1,034.18 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Mar[/TD]
[TD="bgcolor: transparent, align: right"]$975.59 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Total[/TD]
[TD="bgcolor: transparent, align: right"]$2,947.27 [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 174"]
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64" style="width: 48pt;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody>[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 69, bgcolor: transparent, align: right"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][TABLE="width: 174"]
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64" style="width: 48pt;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody>[TR]
[TD="width: 99, bgcolor: transparent"]Bonus Amount[/TD]
[TD="width: 64, bgcolor: transparent"]Jan[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]$1,025.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Feb[/TD]
[TD="bgcolor: transparent, align: right"]$1,034.18 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Mar[/TD]
[TD="bgcolor: transparent, align: right"]$975.59 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Total[/TD]
[TD="bgcolor: transparent, align: right"]$3,034.77 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
The salaries vary. So let's say on a 75,000 dollar salary, the quarterly bonus can pay out no more than 15% or no more than 2,812.50. But if you have a salary of 83,000 the pay out can be no more than 3075.00 Monthly bonus amt earned is below. In option 1 below the quarterly total is more than the allowable amount, option 2 is less.
[TABLE="width: 174"]
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64" style="width: 48pt;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody>[TR]
[TD="width: 99, bgcolor: transparent"]Bonus Amount earned
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]$937.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Feb[/TD]
[TD="bgcolor: transparent, align: right"]$1,034.18 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Mar[/TD]
[TD="bgcolor: transparent, align: right"]$975.59 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Total[/TD]
[TD="bgcolor: transparent, align: right"]$2,947.27 [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 174"]
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64" style="width: 48pt;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody>[TR]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 69, bgcolor: transparent, align: right"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][TABLE="width: 174"]
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64" style="width: 48pt;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody>[TR]
[TD="width: 99, bgcolor: transparent"]Bonus Amount[/TD]
[TD="width: 64, bgcolor: transparent"]Jan[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]$1,025.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Feb[/TD]
[TD="bgcolor: transparent, align: right"]$1,034.18 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Mar[/TD]
[TD="bgcolor: transparent, align: right"]$975.59 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Total[/TD]
[TD="bgcolor: transparent, align: right"]$3,034.77 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
