IFS condition in another iFS

Copper 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 :grinning_face_with_sweat:

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