SOLVED

Ifs function problem

Copper Contributor

so im trying to sort out my expenses, my company has implemented a new system in which the following rules apply.

 

if i work Longer than 5 hours = £5.00 but if end time past 8pm = £15.00

if i work Longer than 10 hours = £10.00 but if end time past 8pm = £20.00

if i work Longer than 15 hours or overnight = £25.00

 

the below formula is for getting the length section correct but im falling short with the >5<10 and the >10<15. i could do this with multiple cells but would like to calculate the days expenses in onn/2 boxes.

Function 1

Q242 is the total hours worked in the day,

R242=IFS((Q242>=15),25,(Q242>5<10),5,(Q242<10>15),10,(Q242<5),0)

 

my second function works okay for calculating if £10 is to be added after 8pm

S242=IF(J242>=1,10,0)

J242 being a time value  for end of working day.

 

and finally if the answer to function 1 is 25 my total is 25, if not its R242+S242

=IF(R242>=25,R242,R242+S242)

 

my second and final formula work great but i just strugle with the first long formula getting correct between values for logic test 2 and 3

 

if anyone has a better way to calculate this id be grateful.

6 Replies
best response confirmed by spudaaa (Copper Contributor)
Solution

@spudaaa 

First function, if use IFS, could be

=IFS(Q242>=15,25,Q242>10,10,Q242>5,5,TRUE,0)

 

@Sergei Baklanvery grateful.

Spent days overthinking this.

@spudaaa , glad to help. Just note that IFS checks conditions one by one from left to right and stops working on first one which returns TRUE.

@spudaaa 

Just to demonstrate the versatility of Excel.

Let the hours worked be called 'hours' and the end time 'clockedOff'

Build three array constants:

worked: ={0;5;10;15}

standard: ={0;5;10;25}

bonus: ={0;15;20;25}

being the time thresholds, the standard rates and the bonus rates for late working.

The pay due is then given by

= LOOKUP( hours, worked, IF( clockedOff>20, bonus, standard ) )

@Sergei Baklan 

i have another question,

so i now have a colum for expesense for each day and a total for the week easily.

 

i now want to work out how much is outstanding to claim between last date claimed and today.

i have a seperate page for existing payements due that have been sent to my office. that contains a cell that has for example

Existing sheet required forumla in yellowExisting sheet required forumla in yellow

I would like to be able to count dates from last claimed until today

im thinking Countif(Range (Column B total for days expense), Criteria (if Column A Between H1-H2)

@spudaaa 

Sorry, I din't catch what exactly is needed. If count dates from last claimed until today - that's 

=TODAY()-H1

and apply General format to the result.

If count number of days with payments

=COUNTIFS(A:A,">"&$H$1,$A:$A,"<="&TODAY(),B:B,">0")

If sum all payments for these days

=SUMIFS(B:B,A:A,">"&$H$1,$A:$A,"<="&TODAY())

 

 

1 best response

Accepted Solutions
best response confirmed by spudaaa (Copper Contributor)
Solution

@spudaaa 

First function, if use IFS, could be

=IFS(Q242>=15,25,Q242>10,10,Q242>5,5,TRUE,0)

 

View solution in original post