SOLVED
Home

Ifs function problem

%3CLINGO-SUB%20id%3D%22lingo-sub-842447%22%20slang%3D%22en-US%22%3EIfs%20function%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842447%22%20slang%3D%22en-US%22%3E%3CP%3Eso%20im%20trying%20to%20sort%20out%20my%20expenses%2C%20my%20company%20has%20implemented%20a%20new%20system%20in%20which%20the%20following%20rules%20apply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20i%20work%20Longer%20than%205%20hours%20%3D%20%C2%A35.00%20but%20if%20end%20time%20past%208pm%20%3D%20%C2%A315.00%3C%2FP%3E%3CP%3Eif%20i%20work%20Longer%20than%2010%20hours%20%3D%20%C2%A310.00%20but%20if%20end%20time%20past%208pm%20%3D%20%C2%A320.00%3C%2FP%3E%3CP%3Eif%20i%20work%20Longer%20than%2015%20hours%20or%20overnight%20%3D%20%C2%A325.00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20below%20formula%20is%20for%20getting%20the%20length%20section%20correct%20but%20im%20falling%20short%20with%20the%20%26gt%3B5%26lt%3B10%20and%20the%20%26gt%3B10%26lt%3B15.%20i%20could%20do%20this%20with%20multiple%20cells%20but%20would%20like%20to%20calculate%20the%20days%20expenses%20in%20onn%2F2%20boxes.%3C%2FP%3E%3CP%3EFunction%201%3C%2FP%3E%3CP%3EQ242%20is%20the%20total%20hours%20worked%20in%20the%20day%2C%3C%2FP%3E%3CP%3ER242%3DIFS((Q242%26gt%3B%3D15)%2C25%2C(Q242%26gt%3B5%26lt%3B10)%2C5%2C(Q242%26lt%3B10%26gt%3B15)%2C10%2C(Q242%26lt%3B5)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20second%20function%20works%20okay%20for%20calculating%20if%20%C2%A310%20is%20to%20be%20added%20after%208pm%3C%2FP%3E%3CP%3ES242%3DIF(J242%26gt%3B%3D1%2C10%2C0)%3C%2FP%3E%3CP%3EJ242%20being%20a%20time%20value%26nbsp%3B%20for%20end%20of%20working%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20finally%20if%20the%20answer%20to%20function%201%20is%2025%20my%20total%20is%2025%2C%20if%20not%20its%20R242%2BS242%3C%2FP%3E%3CP%3E%3DIF(R242%26gt%3B%3D25%2CR242%2CR242%2BS242)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20second%20and%20final%20formula%20work%20great%20but%20i%20just%20strugle%20with%20the%20first%20long%20formula%20getting%20correct%20between%20values%20for%20logic%20test%202%20and%203%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20anyone%20has%20a%20better%20way%20to%20calculate%20this%20id%20be%20grateful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-842447%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842466%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20function%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405219%22%20target%3D%22_blank%22%3E%40spudaaa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%20function%2C%20if%20use%20IFS%2C%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFS(Q242%26gt%3B%3D15%2C25%2CQ242%26gt%3B10%2C10%2CQ242%26gt%3B5%2C5%2CTRUE%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842481%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20function%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3Every%20grateful.%3C%2FP%3E%3CP%3ESpent%20days%20overthinking%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842507%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20function%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842507%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405219%22%20target%3D%22_blank%22%3E%40spudaaa%3C%2FA%3E%26nbsp%3B%2C%20glad%20to%20help.%20Just%20note%20that%20IFS%20checks%20conditions%20one%20by%20one%20from%20left%20to%20right%20and%20stops%20working%20on%20first%20one%20which%20returns%20TRUE.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842509%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20function%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405219%22%20target%3D%22_blank%22%3E%40spudaaa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20demonstrate%20the%20versatility%20of%20Excel.%3C%2FP%3E%3CP%3ELet%20the%20hours%20worked%20be%20called%20'%3CFONT%20color%3D%22%230000ff%22%3E%3CSTRONG%3Ehours%3C%2FSTRONG%3E%3C%2FFONT%3E'%20and%20the%20end%20time%20'%3CFONT%20color%3D%22%230000ff%22%3E%3CSTRONG%3EclockedOff%3C%2FSTRONG%3E%3C%2FFONT%3E'%3C%2FP%3E%3CP%3EBuild%20three%20array%20constants%3A%3C%2FP%3E%3CP%3E%3CFONT%3E%3CFONT%20color%3D%22%230000ff%22%3E%3CSTRONG%3Eworked%3C%2FSTRONG%3E%3C%2FFONT%3E%3A%20%3CSTRONG%3E%3D%7B0%3B5%3B10%3B15%7D%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CSTRONG%3E%3CFONT%20color%3D%22%230000ff%22%3Estandard%3C%2FFONT%3E%3C%2FSTRONG%3E%3A%20%3CSTRONG%3E%3D%7B0%3B5%3B10%3B25%7D%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CFONT%20color%3D%22%230000ff%22%3E%3CSTRONG%3Ebonus%3C%2FSTRONG%3E%3C%2FFONT%3E%3A%20%3CSTRONG%3E%3D%7B0%3B15%3B20%3B25%7D%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ebeing%20the%20time%20thresholds%2C%20the%20standard%20rates%20and%20the%20bonus%20rates%20for%20late%20working.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EThe%20pay%20due%20is%20then%20given%20by%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20LOOKUP(%20%3CFONT%20color%3D%22%230000ff%22%3Ehours%3C%2FFONT%3E%2C%20%3CFONT%20color%3D%22%230000ff%22%3Eworked%3C%2FFONT%3E%2C%20IF(%20%3CFONT%20color%3D%22%230000ff%22%3EclockedOff%3C%2FFONT%3E%26gt%3B20%2C%20%3CFONT%20color%3D%22%230000ff%22%3Ebonus%3C%2FFONT%3E%2C%20%3CFONT%20color%3D%22%230000ff%22%3Estandard%3C%2FFONT%3E)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843794%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20function%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20another%20question%2C%3C%2FP%3E%3CP%3Eso%20i%20now%20have%20a%20colum%20for%20expesense%20for%20each%20day%20and%20a%20total%20for%20the%20week%20easily.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20now%20want%20to%20work%20out%20how%20much%20is%20outstanding%20to%20claim%20between%20last%20date%20claimed%20and%20today.%3C%2FP%3E%3CP%3Ei%20have%20a%20seperate%20page%20for%20existing%20payements%20due%20that%20have%20been%20sent%20to%20my%20office.%20that%20contains%20a%20cell%20that%20has%20for%20example%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130712iD269DDE32FB59338%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-09-09%2012_49_57-2019%20Tracker.xlsm%20-%20Excel.jpg%22%20title%3D%222019-09-09%2012_49_57-2019%20Tracker.xlsm%20-%20Excel.jpg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExisting%20sheet%20required%20forumla%20in%20yellow%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20would%20like%20to%20be%20able%20to%20count%20dates%20from%20last%20claimed%20until%20today%3C%2FP%3E%3CP%3Eim%20thinking%20Countif(Range%20(Column%20B%20total%20for%20days%20expense)%2C%20Criteria%20(if%20Column%20A%20Between%20H1-H2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847501%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20function%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847501%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405219%22%20target%3D%22_blank%22%3E%40spudaaa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20din't%20catch%20what%20exactly%20is%20needed.%20If%26nbsp%3B%3CSPAN%3Ecount%20dates%20from%20last%20claimed%20until%20today%20-%20that's%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTODAY()-H1%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%3Eand%20apply%20General%20format%20to%20the%20result.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIf%20count%20number%20of%20days%20with%20payments%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(A%3AA%2C%22%26gt%3B%22%26amp%3B%24H%241%2C%24A%3A%24A%2C%22%26lt%3B%3D%22%26amp%3BTODAY()%2CB%3AB%2C%22%26gt%3B0%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%3EIf%20sum%20all%20payments%20for%20these%20days%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMIFS(B%3AB%2CA%3AA%2C%22%26gt%3B%22%26amp%3B%24H%241%2C%24A%3A%24A%2C%22%26lt%3B%3D%22%26amp%3BTODAY())%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
spudaaa
New 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
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

2019-09-09 12_49_57-2019 Tracker.xlsm - Excel.jpgExisting 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())

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies