Check out the recording of our latest webinar, The Top 10 Excel Formulas. We didn’t get time for all your questions live, so in this blog we’ll go over the whole lot.
Can you please go through the SUBTOTAL function as well?
SUBTOTAL is a funny little function that can emulate the behaviour of several other common functions, except it ignores hidden rows. It looks like this:
=SUBTOTAL(number, range)
The number is picked from a dropdown and indicates which other function SUBTOTAL will emulate – e.g. 1 is AVERAGE.
Hello, what is the difference between MID and RIGHT please?
RIGHT pulls text from a string starting at the right edge and moving left; where MID starts at the middle and pulls moving to the right.
What is difference between SUMIF and SUMIFS?
SUMIF can only take one criteria range and criterion to check, but SUMIFS can take more than one pair of criteria. They also have their inputs in a different order; I recommend always using SUMIFS because then it’s easier to add further conditions later on if it’s ever necessary.
How does the TODAY function differ from NOW?
TODAY just returns today’s date, whereas NOW also returns the current time. Both recalculate and update automatically.
How do you provide a "if false" statement using IFS?
IFS is just a sequential series of different “if true” statements; if you want a catch-all at the end, use a test that will always be true, like TRUE or 1=1.
Will the XLOOKUP formulas still be maintained is we were to open the Excel sheet on a computer without Office365?
No, the function is not backwards-compatible.
Is there a formula which returns the last month-end? For example, today is 6th July, is there a simple formula which will return 30th June?
You can do:
=EOMONTH(input date, -1)
How can you show the file name on the worksheet?
CELL can help here:
=CELL(“filename”, A1)
When using IFS does the order of the IFS matter?
Yes – the tests are evaluated sequentially, and the formula will return the first true one it comes across.
INDEX lookup can be extended to a 2 dimensional lookup in the sense that it can look up values in a column and a header row in the same formula. Can XLOOKUP do the same?
No – although if you have Office 365 there’s also an equivalent XMATCH that can help improve your INDEX double-MATCH functions!
I need a function that rounds up to the nearest 0.5 - how can you do this?
You can do this with:
=CEILING.MATH(A1, 0.5)