Calculating values against the criteria detailed within this message ...

[BUSJEDWA]

Spreadsheet is used to calculated the amount of time employees can sell – the rules are all employees must take at least 150 hours off, and can sell a maximum of 37.5 hours.

So in the spreadsheet attached, I want column M “entitled to sell” to calculate the amount the individual can sell.

Cell M2 for Joe Bloggs, correctly states he has 37.5 hours he is entitled to sell, as he’s entitled to 187.5 hours, and has 150 remaining.

However, I need to alter the formulae, as Bart Simpson is showing at 75 hours – I want this to display the maximum amount (37.5).

Similarly, mickey mouse is showing as -56, this should be zero – I don’t want to show minus figures.

Donald Duck should show 27.5 hours as this is all he has remaining.

Been very appreciative if someone can assist!!

[oldchippy]

May be something like this,

=IF(L2<=37.5,L2,IF(SUM(H2-150)<=0,0,IF(SUM(H2-150)<=37.5,SUM(H2-150),37.5)))

[NBVC]

does this formula in M2 copied down do as you require?

=MAX(0,MIN(H2-150,37.5,L2))

[BUSJEDWA]

that looks to be the one, thanks very much!

if you don't mind, can you translate in english, i can figure some of it. That way, i'll learn for future tasks!!

[NBVC]

that looks to be the one, thanks very much!

if you don't mind, can you translate in english, i can figure some of it. That way, i'll learn for future tasks!!

Which one?