 • 470K Members
• 5,908 Online
• 568K Conversations
SOLVED

Highlighted

# Ifs function problem

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
Solution

# Re: Ifs function problem

First function, if use IFS, could be

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

# Re: Ifs function problem

@Sergei Baklanvery grateful.

Spent days overthinking this.

# Re: Ifs function problem

@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.

# Re: Ifs function problem

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 ) )

# Re: Ifs function problem

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 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)

# Re: Ifs function problem

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())``

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies