I can't remember how my formula works

[clarer1]

I'm trying to add to spreadsheet I made a couple of years ago. I need to tweak a formula to put something similar-ish in another cell. I know what the formula ends up producing but can't remember how it works.

Can anyone translate each of the steps in this for me please?

=IFS(HLOOKUP(TODAY(),HABITS,19,FALSE)="",HLOOKUP(TODAY()-1,HABITS,20,FALSE),
HLOOKUP(TODAY(),HABITS,19,FALSE)="n",0,
HLOOKUP(TODAY(),HABITS,19,FALSE)="y",(HLOOKUP(TODAY()-1,HABITS,20,FALSE)+1))

HABITS is a Name.
Columns are dates
Cells in row 19 are blank or y or n
Row 20 are numbers. (In case it's relevant, the formula in row 20 counts how many y I've just had without a gap in row 19.)

If today's cell in row 19 contains y, the formula generates the number in today's row 20.
If today's cell in row 19 contains n, the formula generates 0
If today's cell in row 19 is still blank, the formula generates the number in yesterday's row 20.

If I can understand how this formula works, I'll be able to build my new one - I hope!

I'd be grateful if someone could translate each bit for me. Thank you.

Clare

 

[StephenCrump]

This formula:

=IFS(HLOOKUP(TODAY(),HABITS,19,FALSE)="",HLOOKUP(TODAY()-1,HABITS,20,FALSE),
HLOOKUP(TODAY(),HABITS,19,FALSE)="n",0,
HLOOKUP(TODAY(),HABITS,19,FALSE)="y",(HLOOKUP(TODAY()-1,HABITS,20,FALSE)+1))

means:

1. If HLOOKUP(TODAY(),HABITS,19,FALSE)="" then return the value HLOOKUP(TODAY()-1,HABITS,20,FALSE)

else (if condition 1 is not true)
2. If HLOOKUP(TODAY(),HABITS,19,FALSE)="n" then return the value 0

else (if condition 1 and condition 2 are both not true)
3. If HLOOKUP(TODAY(),HABITS,19,FALSE)="y" then return the value HLOOKUP(TODAY()-1,HABITS,20,FALSE)+1.

Assuming TODAY() is 24 September 2018, then HLOOKUP(TODAY(),HABITS,19,FALSE) refers to the cell highlighted in yellow. The HLOOKUP first finds the lookup_value parameter (i.e. TODAY()) in Row 1 of Habits, and then takes the value in the specified row_index_num (i.e. 19)

So your IFS() function is testing the value in the yellow cell.

If this yellow cell value is blank (Test 1 above) the function will return the value in HLOOKUP(TODAY()-1,HABITS,20,FALSE) i.e. the blue cell highlighted, i.e. 11.

Alternatively, if this yellow cell value ="n" (Test 2 above) the function will return 0.

Or finally if this yellow cell value = "y" (Test 3) the function will return the value in the blue cell highlighted + 1, i.e. 11 + 1 =12.

Book1
	B	C	D	E	F	G
1	Habits:
2	Row 1	21 Sep 18	22 Sep 18	23 Sep 18	24 Sep 18	25 Sep 18
3	Row 2
4	Row 3
5	Row 4
6	Row 5
7	Row 6
8	Row 7
9	Row 8
10	Row 9
11	Row 10
12	Row 11
13	Row 12
14	Row 13
15	Row 14
16	Row 15
17	Row 16
18	Row 17
19	Row 18
20	Row 19		y	n		y
21	Row 20		22	11		33
Sheet1