Hello all,
Good grief, I've had nothing but trouble with this spreadsheet! I'll be SO glad when it's finally finished and we can go on to something else...

This formula is using the same thought process as my question in an earlier post http://www.excelbanter.com/showthrea...hlight=txheart

This is an explanation of what my cells are doing
D20=His monthly cost (234.25), F20=Hers monthly cost (234.25)
x12= to get the yearly cost
x0.75 this amount is the yearly cost minus either 25% or 500, whichever is lower)
D22=His current age in years (59), F22=Hers current age in years (59)
(79-D22)=years left in life using national average for him, hers is 83.

My Formula now: =MIN(D20*12*.75),500*(79-D22)+=MIN(F20*12*.75),500*(70*F22)

Typing it out that way I think I see where my problem is, but I'm only more confused now instead of less.
I'm telling the formula that I need the yearly cost times 75% up to a maximum of 500. I need the remaining 25% to be what is maxed at 500. The yearly maximum a person can discount is 500 or 25%. To arrive at their yearly cost after discount I'd need to somehow tell the formula...

25% of the yearly cost would be 702.25. Since this amount is higher than the allowed 500, we must use 500 instead. That is the amount of the yearly discount allowed.

2811-500=2311 this is the amount I need to use now
2311*20 (the number of years left in the average lifespan taken from D22 and F22 above in the explanations)=46220
His lifetime cost minus the yearly 25% or 500 is 46220
Now do hers using the same formula, except average lifespan is 83, not 79)

2311 (the total of the formula we came up with on the man's) * 24 (the number of years left in her average lifespan of 83) = 55464

His total plus hers is 101684.00
The total they will pay up to age M79, F83 with their 25% yearly discount or 500.

This is the amount that I need to come up with, does anyone have the slightest idea how to get there?

Thank all of you for the help you've given me so far on this spreadsheet!


Txheart

try this (works in Excel 2002PL):

D20=His monthly cost (234.25), F20=Hers monthly cost (234.25)
D22=His current age in years (59), F22=Hers current age in years (59)


=(79-D22)*(12*D20-MIN(12*D20*25%;500)) +
(83-F22)*(12*F20-MIN(12*F20*25%;500))

--
==============================
Janusz Pawlinka

Perfect! OMG, thank you so much! I can't believe my 6 paragraph explanation can be said in that little formula! hehe