Sheet formula efficiencies

Derek Hermsen

Hi,

I have a feeling that I should be using "@cell" or "@row" in a formula to make less calculations.  Can anyone shed light on a better formula than the one I have started? Basically trying to determine if a date falls between two other dates through a range (i.e. find out which year/quarter that a specific dates falls within). Screenshot:

Andrée Starå

Hi Derek,

Can you maybe share the formula/sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Derek Hermsen

Does this screenshot help which shows the column headings?

Derek Hermsen

I figured out a work around that made it a bit easier for determining the year & month and for which quarter it falls into; and then added a "Q" to make the cell read the year and quarter (like 2016 Q2).

=YEAR([Lease Date]24) + " " + "Q" + (IF(AND(MONTH([Lease Date]24) > 0, MONTH([Lease Date]24) < 4), 1, IF(AND(MONTH([Lease Date]24) > 3, MONTH([Lease Date]24) < 7), 2, IF(AND(MONTH([Lease Date]24) > 6, MONTH([Lease Date]24) < 10), 3, IF(AND(MONTH([Lease Date]24) > 9, MONTH([Lease Date]24) < 13), 4)))))

Andrée Starå

Nice work! 

If the row with the formula would be on the same row as most cells that you reference, I'd also recommend using the @row function because it makes it easier to work with and takes fewer resources.

Something like this:

=YEAR([Lease Date]@row) + " " + "Q" + (IF(AND(MONTH([Lease Date]@row) > 0, MONTH([Lease Date]@row) < 4), 1, IF(AND(MONTH([Lease Date]@row) > 3, MONTH([Lease Date]@row) < 7), 2, IF(AND(MONTH([Lease Date]@row) > 6, MONTH([Lease Date]@row) < 10), 3, IF(AND(MONTH([Lease Date]@row) > 9, MONTH([Lease Date]@row) < 13), 4)))=YEAR([Lease Date]@row) + " " + "Q" + (IF(AND(MONTH([Lease Date]@row) > 0, MONTH([Lease Date]@row) < 4), 1, IF(AND(MONTH([Lease Date]@row) > 3, MONTH([Lease Date]@row) < 7), 2, IF(AND(MONTH([Lease Date]@row) > 6, MONTH([Lease Date]@row) < 10), 3, IF(AND(MONTH([Lease Date]@row) > 9, MONTH([Lease Date]@row) < 13), 4)))

Paul Newcome

You can also increase efficiency by leveraging the fact that a nested IF will stop at it's first true value. If it makes it further into the formula, then all previous statements must be false. You can also combine the space and the Q.

 

So the first IF would be less than or equal to 3. The second IF could just be less than or equal to 6 because by default it must be greater than 3 to make it this far. So on and so forth.

.

=YEAR([Lease Date]@row) + " Q" + IF(MONTH([Lease Date]@row) <= 3, 1, IF(MONTH([Lease Date]@row) <= 6, 2, IF(MONTH([Lease Date]@row) <= 9, 3, 4)))