I can't remember how my formula works

clarer1

New Member
Joined
Nov 19, 2016
Messages
12
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
BCDEFG
1Habits:
2Row 121 Sep 1822 Sep 1823 Sep 1824 Sep 1825 Sep 18
3Row 2
4Row 3
5Row 4
6Row 5
7Row 6
8Row 7
9Row 8
10Row 9
11Row 10
12Row 11
13Row 12
14Row 13
15Row 14
16Row 15
17Row 16
18Row 17
19Row 18
20Row 19yny
21Row 20221133
Sheet1
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top