Logic Formula trouble

**lmb23** · 10-27-2009, 12:01 AM

I'm a mediocre excel user and trying to acheive the following, but having a bit of difficulty. I am using a series of Ifs and SumIfs, but only able to achieve 2 of the 3 outcomes. Please help.

Here is the scenario,I need to calculate unemployment for each employee in my company per pay period, capped at $8500 for the year. So I need a formula which will calculate it for each period and return one of the following results, 1) If current period unemployment plus all previous paid for the year is less than $8500, then return current period, 2) but if all amounts previously paid is less than 8500 but greater than 8500 when including current period, then return the difference of 8500 and all previously paid, therefore the sum now would be equal to 8500 3) sum of all previously paid is equal to 8500 then return 0.

**Richard Buttrey** · 10-27-2009, 12:28 AM

Hi,

A little difficult to tell without seeing an actual example workbook, so if this is not what you want can you upload one? Also there seems an inconsistency in your 2) statement which is implying both a calculation and also an absolute result of 8500.

However, assuming A1:L1 are the current year periods and I1 is the current period and J1:L1 are still empty.

Please Login or Register  to view this content.

[CODE]

The only nuisance factor with this is that you need to change the I1 reference each new period. The way to avoid this is to hold the current period in another cell and use that to find the current period from a range of column period labels and build that into the formula so that it will work automatically for any current period. But you need to post an actual example to advise further.

Rgds