Formula Friday #14
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
Get a hot beverage, a piece of paper and a pen. This is the post where I try to explain how nested IFs work. Wish me luck.
What is a nested IF
A nested IF is a formula that combines two or more IF statements. More specifically, one, or more, IF statements are contained inside of another IF statement. And the results are dependent on the outcome of the first IF statement.
Google might give you a different definition but this is the one that makes the most sense to me.
Nesting isn’t unique to IF statements
It is something that is very common when writing excel formulas. All it is, is combining two functions, one inside the other, so that the outcome of one function is dependent on the result of another.
A really great example of a function that is frequently used in nesting is IFERROR. I use it so freely I often misspell it because, at times, my fingers move faster than my brains processors.
This is an example of what nesting looks like when using IFERROR
In this example the result of the VLOOKUP will determine the outcome of the IFERROR. If the VLOOKUP does not result in an error, the outcome for the IFERROR will be the result of the VLOOKUP. But if the VLOOKUP does result in an error, the outcome of the IFERROR will be ““ – also known as a blank cell.
If you’re still with me we’ll carry on
I believe the term nested IFs came about because it is an intentional action to join multiple IF statements together. They become the instructions for Excel to use to determine what to do with the different variations of data.
An easier way to explain it is with an example. We’ll cover a simple or what I would call a “one-step nested IF” first.
In our first IF statement our logical test is, is D8 equal to blank. If this is true and D8 is equal to blank input TBC, if it is false and not equal to blank, move onto our second IF statement.
The logical test in our second IF statement is, is C8 equal to D8. If this is true and C8 is equal to D8 input no change, if it is false and C8 does not equal D8 input moved.
When working with nested IFs, I find it a lot easier to say aloud what I am asking Excel to do. Which does make it appear as if I am talking to myself, when really I’m talking to Excel.
Another reason why it’s handy to talk to Excel while writing nested IFs is because they can get very confusing very quickly.
Let’s take things up a notch
We are still going to be working with a one-step nested IF. But it’s a lot chunkier than the example we just covered.
Let’s work through an example together. We’ll calculate our net cost price depending on whether or not the cost price given is “Landed” – non FOB – or FOB.
The difference between Landed and FOB is ownership of the goods. With Landed cost prices the supplier is responsible for the order until it arrives at the agreed delivery point. Meaning they will pay the freight and duty charges. With FOB the supplier is responsible for the order until it arrives at the port. And as such, with FOB prices you are given, are likely to exclude freight and duty.
It is the responsibility of the supplier to prepay for freight and duty (transportation charges). This cost is then recuperated from the buyer, usually by sending an invoice after the goods have shipped.
Therefore, in order to get a “true” cost of the FOB order, and check if you are keeping to your margin targets. Approximate transportation charges need to be added on so that these are included in any margin calculations.
I feel it’s important to say the above definition is what I believe to be true, from my experience in working for large retailers. And may not be the official word, especially as things change so often. So if you need the most up to date information I would suggest checking with the relevant authorities to ensure you are fully compliant.
This post was first written in December 2020 so different rules may apply now.
Onto the example
The first thing we need to determine is whether the cost price given is Landed or FOB. Our logical test would look like this
If the cost price is landed it’s likely to already be in GBP so there is nothing more to do with the cost price at this stage.
If however the cost price is not Landed, and therefore FOB, it’s likely to be given in USD. Which means we will first need to convert the cost price into GBP.
The exchange rates, as we know, fluctuate constantly so for the sake of this example we are working with a 1.5 exchange rate.
And let’s say every FOB order, with a cost price that is greater than or equal to £100 gets a 5% discount.
But don’t forget we need to add on the transportation costs.
Let’s pretend it’s 2% of the cost price for anything under £100 and 5% of the cost price for anything over £100. So basically the “discount” you get for the over £100 cost price is wiped out.
I just want to say again none of this is real life, it’s purely for this example.
So that nested IF go messy real quick
And. It all fit within a one-step nested IF!
The complexity of a nested IF increases depending on the number of instructions given.
Which is why it’s okay to talk to Excel as you write out nested IFs. If for no other reason than to check you are keeping a track to whether the input instructions will get to the desired outcome.
But always remember the longer they get the messier they look.
The below is an example of what I would call a two-step nested IF
It’s a relatively simple formula. But there are a lot of instructions, which can make you feel as if you can’t see where the formula starts and where it ends.
Because the instructions repeat through the formula. It puts it in the “simplistic” column, although still messy and at times hard to read.
The instruction for each of the nested IFs makes use of the SUMIF function. It is used to add together the values of a specific column, which changes between each IF statement, When the specified criteria are a match, which stay consistent between each IF statement.
It says, if cell $D$8 – the selling period – is “July-August”, add together the values of a specific range, LD!$DC$11:$DC$313, when the selling period matches in column BB – LD!$BB$11:$BB$313,$D$8.
If however, the selling period is not “July-August” and it is in fact “September”. Add together the values of a specific range, LD!$DD$11:$DD$313, when the selling period matches in column BB.
If the selling period is neither “July-August” or “September” and is instead “October”. Add together the values of a specific range, LD!$DE$11:$DE$313, when the selling period matches in column BB.
When it comes to nested IFs take your time. When I first started using them I would write out what I needed the formula to do. So that I could test each different outcome as I went along. Because trying to unravel a nested IF when you’re not sure what each of the stages are is a nightmare. I’m speaking from experience.
If you feel as if you could do with adding in a few nested IFs to your work, but would like some extra support to set them up, feel free to drop me a line. And for the rest of you, good luck and may the Excel Gods be forever in your favour.
Until next time,