IFS condition in another iFS

%3CLINGO-SUB%20id%3D%22lingo-sub-3072654%22%20slang%3D%22fr-FR%22%3EIFS%20condition%20in%20another%20iFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3072654%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3Eim%20kind%20of%20struggling%20writting%20an%20IFS%20into%20an%20IFS.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20calculte%20the%20number%20of%20sets%2C%20depending%20on%20%3A%3C%2FP%3E%3CP%3E-%20if%20the%20cell%20C14%20is%20equal%20to%200%2C%201%20or%202%20(0%20for%20squat%2C%201%20for%20bench%2C%202%20for%20deadlift%2C%20but%20it%20never%20works%20if%20i%20write%20text)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sheet%20%26quot%3BWeeks%201-4%26quot%3B%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342581i89B1AFB8DB43ABD1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-01-26%20at%2015.43.21.png%22%20alt%3D%22Sheet%20%26quot%3BWeeks%201-4%26quot%3B%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESheet%20%22Weeks%201-4%22%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E-%20and%20depending%20the%20number%20in%20C14%2C%20if%20i%20choose%201%2C2%20or%203%20in%20Recovery%20(another%20sheet)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sheet%20%26quot%3BSettings%26quot%3B%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342582i7082DEE5A1021910%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-01-26%20at%2015.45.52.png%22%20alt%3D%22Sheet%20%26quot%3BSettings%26quot%3B%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESheet%20%22Settings%22%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20hope%20i%20explained%20myself%20well%20%3Avisage_souriant_avec_une_goutte_de_sueur%3A%3C%2FP%3E%3CP%3Ehere%20is%20the%20formula%20that%20i%20used%20to%20calculate%20the%20number%20of%20sets%2C%20depending%20what%20you%20choose%20for%20recovery%20%3A%20%3CSPAN%20class%3D%22%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3EIFS%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E(%3C%2FSPAN%3E%3CSPAN%3ESettings!%24D%2419%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E%3B%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3E1%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E%3C%2FSPAN%3E%20%3CSPAN%3ESettings!%24D%2419%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E2%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3E2%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E%3C%2FSPAN%3E%20%3CSPAN%3ESettings!%24D%2419%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E3%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3E3%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20for%20your%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%20(im%20working%20in%20excel%20sheets)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3072654%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi, 

im kind of struggling writting an IFS into an IFS.

I would like to calculte the number of sets, depending on :

- if the cell C14 is equal to 0, 1 or 2 (0 for squat, 1 for bench, 2 for deadlift, but it never works if i write text)

Sheet "Weeks 1-4"Sheet "Weeks 1-4"

- and depending the number in C14, if i choose 1,2 or 3 in Recovery (another sheet)

Sheet "Settings"Sheet "Settings"

I hope i explained myself well

here is the formula that i used to calculate the number of sets, depending what you choose for recovery : =IFS(Settings!$D$19=1;1;Settings!$D$19=2;2;Settings!$D$19=3;3)

 

thanks for your help (im working in excel sheets)

4 Replies

@Amandine78 

Sorry, but it's totally unclear, at least for me

I would like to calculte the number of sets

How do you calculate sun?

 

if the cell C14 is equal to 0, 1 or 2 (0 for squat, 1 for bench, 2 for deadlift, but it never works if i write text)

Where do you write text?

 

and depending the number in C14

What the dependence is ?

 

here is the formula that i used to calculate the number of sets, depending what you choose for recovery : =IFS(Settings!$D$19=1;1;Settings!$D$19=2;2;Settings!$D$19=3;3)

I see no summing here. Where this formula is ? By the way, on practice equivalent could be =Settings!$D$19

I think the IFS formula is going in cell F14 and based on the value of D14 it should look up the corresponding value in Settings!$D$17:$D$19. My first point is that D14 should be TEXT to match the TEXT in Settings!$C$17:$C$19 and then use XLOOKUP
=XLOOKUP(D14,Settings!$C$17:$C$19,Settings!$D$17:$D$19,"incorrect selection",0)

@Sergei Baklan 

i'll explain the whole thing hoping its more understandable.

I want to calculate how many sets i'll train (in F14). For that, in sheet Settings, i can choose a number (1,2 or 3) according my recovery for each lift : 1: poor recovery, 2: good recovery, : 3: very good recovery. 

For now thats how i calculated it => cell F12 for squat: =IFS(Settings!$D$17=1;2;Settings!$D$17=2;3;Settings!$D$17=3;4)  (2, 3 and 4 are sepcified by me)

as i choose 1 for recovery, I get 2 in F12. (the formula works).

 

so for each lift, i ajdust the formula to match the good cell (D18 or D19 in Settings), writting the number of sets according the recovery number i chose.

 

But now, what i want to do is to create a Data Validation in F14, giving the choice between: Squat, Bench, Deadlift.

And depending what i choose (Squat for example), excel will give me how many sets i should do ACCORDING my recovery.

=> Squat in D14 with 1 in D17 (Settings) will give me 2 sets to do ( because i said so, i could specify 3 sets).

 

Hope its clearer now ^^

Hi, thanks for your reply.
I played with XLOOKUP, but i always end up with #error