Please or to access all these features

AIBU?

Share your dilemmas and get honest opinions from other Mumsnetters.

Can anyone help write excel nested if statement formula

39 replies

Sashimiandhisthunderpaws · 27/09/2022 21:57

I'm really stumped trying to fix/write a formula and I'm not having any luck on Google/Youtube. I'm hoping that there is someone that can help me.

I'm trying to use a nested if statement to flag if multiple conditions are true. So first column is supplier and the second is cost centre. Our cost centre format is ####/####, eg 5000/5050. So I want to flag specified suppliers and cost centre combinations, eg supplier Asda and the cost centre either starting with 5999/, 6999 or 7.

Does anyone know how to do this? I've been messing around with search and find, wildcards.

OP posts:
Hairymaery · 27/09/2022 22:17

Screenshot and I will help

titchy · 27/09/2022 22:14

foobio · 27/09/2022 22:05

When learning excel, don't jump straight to nesting formulae, use separate columns to do each stage of the calculation one at a time. Once you've got to a column with your answer, you can then (in another column) nest together the steps. (Or just hide the steps and keep the final column).

This!!! You end up with missing brackets somewhere otherwise. Far easier to do complex nested formula in section.

Sashimiandhisthunderpaws · 27/09/2022 22:13

I haven't tried vlookup/iserror. I've started with if, and, or, search. Will try to screen shot with the formula shown. I'll show

I'm trying to get the cost centre check to work before i insert into the if statement.

OP posts:
cakeorwine · 27/09/2022 22:11

It's an IF and AND statement.

IF this is ASDA AND the cost centre is 123, then THIS, else ....

But if you have lots of cost centres and lots of suppliers, then it gets way more complicated.

How many combinations do you have?

Getoff · 27/09/2022 22:07

Although if you are setting a flag for each row in a table I would have thought "if" combined with "And" and "Or" functions would do the job.

foobio · 27/09/2022 22:05

When learning excel, don't jump straight to nesting formulae, use separate columns to do each stage of the calculation one at a time. Once you've got to a column with your answer, you can then (in another column) nest together the steps. (Or just hide the steps and keep the final column).

ChiefWiggumsBoy · 27/09/2022 22:05

I would post on the excel sub of reddit. I’ve done that before and some clever boff came along and just wrote the formula for me, which was good as I would never have been able to sort it myself!

RiftGibbon · 27/09/2022 22:04

I may be misunderstanding what you're trying to do but I taught myself how to only count entries if multiple conditions were true.
This was using COUNTIFS rather than COUNTIF , and in the example I was working on, I wanted to count only if [as an example] A1-A500 was "Yes", and F1-F500 was "Green".
Is that the sort of thing you're wanting to do, or am I barking up the wrong tree?

lljkk · 27/09/2022 22:04

Using a vlookup & concatenating the other 2 fields might let you have a single look up table for the combinations possible. I agree this is likely to be far easier than a complicated set of of nested IF statements.

Insert a column which concats the supplier & cost centre
pivot table to get a list of unique combinations
next to pivot table entries, put the true/false statements (assuming there's more than one option that could be true/false)
vlookup back to pivot table (+TF) to insert TF option in the active table

I have to copy & paste vlookup syntax every time, it's not as intuitive as I'd like

Getoff · 27/09/2022 22:03

Does Countifs function help?

MostlyHappyMummy · 27/09/2022 22:02

Use AND with IF to combine conditions
like this
=IF(AND(Something is True, Something else is True), Value if True, Value if False)

Midnights · 27/09/2022 21:59

Have you got a screenshot of the excel sheet? Im guessing you've tried IF and AND statements combined?

Oasis1975 · 27/09/2022 21:59

Try Googling iferror(vlookups….

JobQuandree · 27/09/2022 21:59

Could you use a VLookup for this, I have to Google it every time but I think you could use it for this

Swipe left for the next trending thread