From time to time I get asked the question “what order do formulas evaluate?”

The acronym BEDMAS can help you remember. It stands for:

**B**rackets: Any operation(s) contained in brackets will be carried out first followed by any exponents.

**E**xponents: Then any exponents like ^ or SQRT

**D**ivision or **M**ultiplication (left to right): Excel considers these to be of equal importance, and carries out these operations in the order they occur from left to right in the equation.

**A**ddition or **S**ubtraction: The same goes for addition and subtraction. They are considered equal in the order of operations. Whichever one appears first in an equation, either addition or subtraction, is the operation carried out first.

Ok, so what if you know all that (after all you probably learnt BEDMAS at school way-back-when) but you’re still stuck because your formula isn’t returning the result you want.

Well, thankfully Excel has a tool for that too.

## Evaluate Formula Tool

The Evaluate Formula tool allows us to see how each component of a formula evaluates, one step at a time.

Let’s take the VLOOKUP formula we looked at last week as an example:

## How to use the Evaluate Formula Tool

- Select the cell containing the formula you want to evaluate. Ours is in G6.
- On the
**Formulas tab**of the Ribbon in the**Formula Auditing group**select**Evaluate Formula**.

The Evaluate Formula dialog box will open:

- Click the ‘Evaluate’ button to view the value of the underlined reference. In the example above the underlined reference is cell D6, and you can see below it evaluates to ‘William’.
- If the underlined reference is part of another formula you can use the ‘Step In’ button to display the other formula. Then ‘Step Out’ to go back and continue evaluating.
- In the image below you can see all but the last step of the formula evaluated:
- And finally the result:

The Evaluate Formula tool is especially useful for nested formulas that may not be returning the correct result.

I also like to use it to find why I’m getting # errors or checking that the result is calculating as I expect.

But be warned; it can’t work miracles. If you can’t get Excel to even accept your formula then you may need to consult your office Guru first 🙂

SA

FILTER(‘Finance Tec Competency’!E:E, ISNUMBER(MATCH(‘Finance Tec Competency’!C:C, ‘Finance Tec Competency’!CA$12, 0)))

I want to drag down the above formula but I want to change Finance Tec Competency’!E:E to be Finance Tec Competency’!F:F “””auto”” HOW??

Mynda Treacy

You can’t drag it down because you have whole column references E:E etc. You could drag it across, but I would strongly recommend you replace the whole column references with the actual range + some spare rows to allow for growth or put your source data in an Excel Table. While some Excel functions can now ignore empty cells in whole column references, not all can and sometimes even those that can don’t under certain conditions, so I always avoid them.

If you’re still stuck, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

Adam

Question:

I tried to create a formula to do a Match on two separate columns. This is because we have the same servers (same IP) but different vulnerabilities (Plugin IDs) for them.

This is my formula:

=IF(MATCH(A2,’Existing – Server Application’!$A$2:$A$412,0),AND(MATCH(G2,’Existing – Server Application’!$H$2:$H$412)),”False”)

I get an #N/A error and can’t seem to get around it. What I need is: If cell A2 of “Worksheet 1” matches anything in Column A of “Worksheet 2” and cell G2 of “Worksheet 1” matches same in Column H of “Worksheet 2” then return a True, otherwise False.

Mynda Treacy

Hi Adam,

Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

Mynda

Vishal Jain

Formula Not Working … Please Help …

=IF(

LeadCount!$B5 = “All” ,

IF(

LeadCount!D$2 = “All” ,

SUM(

COUNTIF(

USA!$D:$D,

”Interested”

),

COUNTIF(

USA!$D:$D,

”Forwarded the documents”

),

COUNTIF(

USA!$D:$D,

”Will visit office”

)

) ,

COUNTIF(

USA!$D:$D,

LeadCount!D$2

)

) ,

IF(

LeadCount!D$2 = “All” ,

SUM(

COUNTIFS(

USA!$D:$D,

”Interested”,

USA!$Z:$Z,

LeadCount!$B5

),

COUNTIFS(

USA!$D:$D,

”Forwarded the documents”,

USA!$Z:$Z,

LeadCount!$B5

),

COUNTIFS(

USA!$D:$D,

”Will visit office”,

USA!$Z:$Z,

LeadCount!$B5

)

) ,

COUNTIFS(

USA!$D:$D,

LeadCount!D$2,

USA!$Z:$Z,

LeadCount!$B5

)

)

)

Mynda Treacy

Hi Vishal,

It’s very difficult to troubleshoot a formula like this without the file. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

Mynda

Muhammad Nauman

=(IF(F9>=25,”Critical”), (F9>=20,”High”), (F9>=15,”Medium”,”Low”)))

This formula is not working

Mynda Treacy

Try this:

=IF(F9>=25,”Critical”, IF(F9>=20,”High”, IF(F9>=15,”Medium”,”Low”)))

Mynda

Khem

Please check this formula. =IF(AND(AP24>0,AQ24>0,AH24>0),”BUY CE”,IF(AND(AP24<0,AQ24<0,AH240,AQ4<0,AH24<0),"BUY PE",IF(AND(AP0,AH24>0),”BUY PE”,IF(AND(AP24>0,AQ24>0,AH24<0),"BUY PE")))))

I want to make it happens for all the three AP24,AQ24,AH24. now its considering only AP24 and AQ24.

Mynda Treacy

Hi Khem, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

Rohan Sen

Could you please check these three formulas?

=ARRAYFORMULA(IF(ISBLANK(F2:F),,”S”&Input!$B$1&VLOOKUP(F2:F,’New DRCP Verlauf’!C:P,14,0)&”A”&TEXT(VLOOKUP(F2:F,’New DRCP Verlauf’!C:P,16,0),”0000″)))

=ARRAYFORMULA(IF(ISBLANK(F2:F),,VLOOKUP(F2:F,’New DRCP Verlauf’!C:P,18,0)))

=ARRAYFORMULA(IF(ISBLANK(F2:F),,”S”&Input!$B$1&VLOOKUP(F2:F,’New DRCP Verlauf’!C:P,14,0)&”A”&TEXT(VLOOKUP(F2:F,’New DRCP Verlauf’!C:P,16,0),”@”)))

Mynda Treacy

Hi Rohan,

There’s no such function as ARRAYFORMULA in Excel.

Mynda

NARAYAN

=IF(OR(F4=”EID-UL-FITAR”,F4=”EID-UL-AJAHA”,[@RELO]=”M”,CODE!F5*1,IF(Sheet1!D7>=”H”,F4=”DURGA PUJA”,F4>=”BUDDHA PURNIMA”,CODE!F5*1,IF(F4>=”MONTH BILL”,,CODE!F5*1,””))))

Catalin Bombea

Yes

Grant

Please correct this formula.

=LOOKUP([@Parent],Table37[[Parent]:[Name]],Table37[Name], OR(LOOKUP([@Parent],Table1[[ID]:[Title]],Table1[Title])

I get “The formula is missing an openeing or closing parenthesis” error.

Mynda Treacy

Hi Grant, you can’t structure the formula like that with OR at the end. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

Kenima

please correct

=IF(AND(B1,C1,D1,E1)=1,”nedovoljan”,IF(AND(G1=3.5),”vrlodobar”,IF(AND(G1=2.5),”dobar”,IF(AND(G1=1.5),”dovoljan”,IF(AND(G1<1.5),"nedovoljan","nedovoljan")))))

Mynda Treacy

Hi Kenima,

Each AND argument must be a logical test. You can’t list all cells and then only 1 logical text i.e.:

You also don’t need AND for the remaining IFs because there is only one condition:

Mynda

NAVEED UR REHMAN

=IF(OR(E5=”In House”,KN5>=15,E5=”Outside”,KN5>=25),”Sufficient stock”,”Near to Nill”)

please correct

Catalin Bombea

Hi Naveed,

What is wrong?

Or, how do you want it to work, if it’s not working as expected?

MANGALA MOHANTY

=IF(AND(D7>0,H7>0),”FRESH LONG”,IF(D7<0,H70,H7<0),"FRESH SHORT",IF(AND(D70),”SHORT COVERING”,”)

THIS SYNTAX WONT WORK ,KINDLY RECTIFY THIS FORMULA

Catalin Bombea

Try:

=IF(AND(D7>0,H7>0),"FRESH LONG",IF(AND(D7<0,H7<0),"FRESH 14:14",IF(AND(D7>0),"SHORT COVERING","No Match")))

Make sure you put the logical conditions needed for each nested IF function.

Anup Verma

=IF(AND(D2=K2,”Q1 2022″,IF(D2=H2,”Q2 2022″,IF(D2=I2,”Q3 2022″,IF(D2=J2,”Q4 2022″,””))

Please help me correcting this

Mynda Treacy

Hi Anup,

You haven’t used AND correctly. Try:

However, a more efficient way to write this formula is explained in this IF Function video tutorial.

Mynda

dilshan

=IF(H3>=50,”Pass”,”Fail”),IF(M3>=1,”Repeat”)

pls send me correct one

Mynda Treacy

=IF(M3>1,”Repeat”,IF(H3>=50,”Pass”,”Fail”))

Anu

IF(AND(S2=((“Priority 4”,U2<=2), "Met","Not Met"),(("Priority 5",U2<=7),"Met","Not Met")))

I am not getting the answer

Catalin Bombea

try:

=IF(AND(S2=“Priority 4”,U2<=2), "Met",IF(AND(S2="Priority 5",U2<=7),"Met","Not Met"))

Jotendar

How to write formula in excel : 25% of quantity or Rs.5000 whichever is greater

Catalin Bombea

Hi,

Try:

=MAX(5000,A1*0.25)

Tanvir Khan

Where is my mistakes here in this? this formula excel not excepting. looking your urgent reply please.

=IF(I3=-26,”infinity”,IF(I3=-25,”infinity”,IF(I3=-24,”infinity”),IF(I3=-23,”infinity”),IF(I3=-22,”infinity”),IF(I3=-21,”infinity”),IF(I3=-20,”5.49″),IF(I3=-19,”5″),IF(I3=-18,”3.47″),IF(I3=-17,”4″),IF(I3=-16,”6.84″),IF(I3=-15,”2.24″),IF(I3=-14,”3.53″),IF(I3=-13,”3.23″),IF(I3=-12,”3.23″),IF(I3=-11,”2.93″),IF(I3=-10,”3.2″),IF(I3=-9,”2.65″),IF(I3=-8,”2.38″),IF(I3=-7,”2.53″),IF(I3=-6,”2.62″),IF(I3=-5,”2.46″),IF(I3=-4,”2.48″),IF(I3=-3,”2.26″),IF(I3=-2,”2.2″),IF(I3=-1,”2.26″),IF(I3=0,”2.19″),IF(I3=1,”2.1″),IF(I3=2,”2.02″),IF(I3=3,”2.01″),IF(I3=4,”1.8″),IF(I3=5,”1.94″),IF(I3=6,”2.06″),IF(I3=7,”1.76″),IF(I3=8,”1.76″),IF(I3=9,”1.74″),IF(I3=10,”1.79″),IF(I3=11,”1.59″),IF(I3=12,”1.57″),IF(I3=13,”1.65″),IF(I3=14,”1.54″),IF(I3=15,”1.76″),IF(I3=16,”1.36″),IF(I3=17,”1.49″),IF(I3=18,”1.13″),IF(I3=19,”1.62″),IF(I3=20,”1.2″),IF(I3=21,”1″),IF(I3=22,”2″),IF(I3=23,”1″),IF(I3=24,”1″),IF(I3=25,”1″),IF(I3=26,”1″),IF(I3=27,”1″)))

Catalin Bombea

Hi Tanvir,

You should setup a table with 2 columns with your parameters, it’s not a good idea to use so many nested functions.

With a lookup table, the formula is much simpler: =INDEX(Table1[Result],MATCH(I2,Table1[Value],0))

In your formula, as an example:

=IF(I3=-26,”infinity”,IF(I3=-25,”infinity”,IF(I3=-24,”infinity”

),IF(I3=-23,…there should not be a closing paranthesis before the last IF in this example, all closing paranthesis should be at the end of the formula.

Some older excel versions will not accept too many nested IF’s, you should go for a lookup table.

Uma

if B having annual target 230917 to find monthly target and find if a month having 20 day to calculate per day target

Catalin Bombea

Hi Uma,

Daily target should simply be: =B1/12/20

Mohammad Akbar

Please correct my formula =COUNTIFS(DIS!B:B,{“Taimani”,”chaman”,”Khushalkhan”},DIS!E:E,{“Group Loan”,”GMRB”})

Catalin Bombea

Try:

=COUNTIFS(DIS!B:B,”Taimani”,DIS!B:B,”chaman”,DIS!B:B,”Khushalkhan”,DIS!E:E,”Group Loan”,DIS!E:E,”GMRB”)

lina

=IF(AND(B4>=80,C4>=80,D4>=200),”Passed all”,IF(AND(B4=80,D4=80,C4<80,D4<80),"Passed Science only",IF(AND(B4<80,C4=200),”Passed TOEFL only”,IF(AND(B4>=80,C4>=80,D4<200),"Passed both English and Science",IF(AND(B4=80,D4>=200),”Passed both English and TOEFL”,IF(AND(B4>=80,C4=200),”Passed both Science and TOEFL”))

Mynda Treacy

Did you have a question, Lina?

Dero

Hello,

i think there is something wrong with this formular but i can not identify whats wrong.

=IF($A$5:$A2489=””,””,IF(COUNTIF(‘Scanned Assets’!$A$2:$A2428,$A$5:$A2489)>0,”Y”,”N”))

I have about 2000 asset numbers, but the formular is only reading against 950 cells, so any number after cell 950 is not recognized.

Basically what i am trying to do is – Already have the assets numbers, I want a Y once i scan assets and the number is part of the numbers i already have.

Please can you help?

Mynda Treacy

Hi Dero,

The first argument of the IF formula should only reference one cell, not a range of cells. That said, I don’t think the COUNTIF will be doing what you want either. Please post your question and Excel file on our forum where we can help you further.

Mynda

Tiff

=IFERROR(1-(SUM(IF(D13:D62={“CLI / ANCILLARY”,”ENDOSCOPY”,EMERGENCY ROOM”,”IV THERAPY”,”OB OUTPATIENT”,OBSERVATION”,”RECURRING”,SAME DAY SURGERY”,”WOUND HEALING CLINIC”},H13:H62,0))/SUM(COUNTIF(D13:D62,{“CLI / ANCILLARY”,”ENDOSCOPY”,”EMERGENCY ROOM”,”IV THERAPY”,”OB OUTPATIENT”,”RECURRING”,”SAME DAY SURGERY”,”WOUND HEALING CLINIC”}))),”%”)

I keep getting an error message at D13:D62 I’m sure there’s plenty more none of my sheet with the exception of two columns will populate a % I want. If there is a time I can screenshare and someone help me I’ll of course happily pay for the time. Here is one of 48 other percntages in the same sheet I cannot get to work 🙁

Catalin Bombea

Hi Tiff,

Can you upload to our forum a sample of your data, with a manual example of expected results?

Does not seem like a complicate problem, but we need to see some data to test the formulas.

Catalin

Tiffany upshaw

Uploading now

Glenn

=IF(FIND(“@nscglobal”,[Created by]),”Yes”,IF(FIND(“@axiomtechnologies.com”,[Created by]),”Yes”,IF(FIND(“@se.relacom.com”,[Created by]),”Yes”,IF(FIND(“@DaisyGroup.com”,[Created by]),”Yes”,IF(FIND(“@ecfix.com”,[Created by]),”Yes”,IF(FIND(“@dynacons.com”,[Created by]),”Yes”,”NO”))))))

What’s wrong with this?

Catalin Bombea

Hi Glenn,

The logical test in the IF function expects a boolean data type. FIND(“@nscglobal”,[Created by]) will return 2 different data types: N/A (error), when that text is not found, and a positive number if that text is found. In other words, errors are not handled. Make sure your formula returns the expected data type, use this for example: =ISNUMBER(FIND(“@nscglobal”,[Created by])) instead of FIND(“@nscglobal”,[Created by]). ISNUMBER will return TRUE/FALSE, no matter if FIND function fails.

khalil billeh

=IF(D13=12%,IF(AND(D15>=0%,D15=26%,D15=46%,D15=61%,D15=81%,D15=0%,D15=26%,D15=46%,D15=61%,D15=81%,D15=0%,D15=26%,D15=46%,D15=61%,D15=81%,D15=0%,D15=26%,D15=46%,D15=61%,D15=81%,D15=0%,D15=26%,D15=46%,D15=61%,D15=81%,D15<=100%),"4%")))))))))))))))))))))))))))

Philip Treacy

Hi Khalil,

What is your question?

Regards

Phil

Joe

‘=if(C15=’Tax Not Included’,0,if(and(C15=’Tax on Material Only’,sum(E12:E13)*D15),if(and(C15=’Tax on Labor Only’,sum(E3:E11)*D15,if(and(C15=’Tax on Labor &Material’,sum(E3:E13)*D15))))

Can’t get this to work. Can anyone tell me what is wrong with this?

Mynda Treacy

Hi Joe,

Try wrapping your text in double quotes instead of single quotes. e.g.

Mynda

Chris Dollahite

Hi Mynda,

I frequently use the evaluate formula tool however I cannot resize the evaluate formula tool window. This makes evaluating array formulas particularly hard. Do you know of an evaluate formula add-in with a larger formula evaluation screen or perhaps a way to expand the evaluate formula tool window?

-Chris

Mynda Treacy

Hi Chris,

This is a gripe of us MVPs too. Unfortunately it and many other dialog boxes cannot be resized 🙁

That doesn’t mean we don’t keep asking at every Microsoft Summit when we meet with the Excel developers.

Mynda

Umar Khaled

Can anybody help detecting problem in following formula:

(every time i pressed enter, the result shown:#NAME)

=IF(And(D2=”Dhaka”,F2<5000),F2*70%””, IF(And(D2=”Rajshahi”,F2<5000),F2*65%””, IF(And(D2=”Feni”,F2=10000),F2*60%””, IF(And(D2=”Rajshahi”,F2>=10000),F2*55%””, IF(And(D2=”Feni”,F2>=10000),F2*50%””))))))

Any Syntax Error?

Mynda Treacy

Hi Umar,

The double quotes after the % signs shouldn’t be there e.g. F2*70%””

But without seeing the file it’s hard to say otherwise. You’re welcome to post your question and Excel file on our forum where we can help you further.

Mynda

brandon

IF(((AJ3+AK3+AL3+AM3+AN3+AO3)/6)*70>=70,”70″, “(AJ2+AK2+AL2+AM2+AN2+AO2)/6)*70”)

This is the formula that I am using. I am adding values to figure out a students grade out of 70 without letting the value exceed 70. That is working, I can not get it to use the second formula if it doesn’t exceed 70. Help please.

Mynda Treacy

Hi Brandon,

Looks like too many double quotes. Try this:

Double quotes return/specify text.

Mynda

Robert Prather

Something to remember from our Highschool days.

Here’s a question for you, is there anyway to tell from the error that excel returns (#Name, #Value) as to what might be wrong?

Mynda Treacy

Hi Bobcat,

Yes, you can tell from the error what might be wrong. I haven’t written a list of them but you can find one at the link below (see the right hand side of the page under ‘See also’):

https://office.microsoft.com/en-us/excel-help/correct-common-errors-in-formulas-HA010066323.aspx

I should add writing about that to my To-Do list 🙂

Kind regards,

Mynda.