Forum Discussion
IFS condition in another iFS
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"
- and depending the number in C14, if i choose 1,2 or 3 in Recovery (another sheet)
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
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'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 ^^
- 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)- Hi, thanks for your reply.
I played with XLOOKUP, but i always end up with #error
