• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Evaluate Formula Tool

You are here: Home / Excel Formulas / Excel Evaluate Formula Tool
Excel Evaluate Formula Tool
February 22, 2012 by Mynda Treacy

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

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

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

Exponents: Then any exponents like ^ or SQRT

Division or Multiplication (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.

Addition or Subtraction: 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:

Excel Evaluate Formula Tool

How to use the Evaluate Formula Tool

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

    The Evaluate Formula dialog box will open:

    Excel formula not working
     

  3. 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’.

     

    How Excel formulas work

  4. 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.

     

    How Excel formulas work

  5. In the image below you can see all but the last step of the formula evaluated:

     

    How Excel formulas work

  6. And finally the result:

     

    How Excel formulas work

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 🙂

Excel Evaluate Formula Tool

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:VLOOKUP Multiple CriteriaVLOOKUP Multiple Criteria
Next Post:SUMIFS Formula Referencing Cells Containing DatesSUMIFS Formula Referencing Cells Containing Dates

Reader Interactions

Comments

  1. Vishal Jain

    January 30, 2023 at 11:03 pm

    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
    )
    )
    )

    Reply
    • Mynda Treacy

      January 31, 2023 at 8:02 am

      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

      Reply
  2. Muhammad Nauman

    October 20, 2022 at 10:15 pm

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

    This formula is not working

    Reply
    • Mynda Treacy

      October 21, 2022 at 6:37 am

      Try this:

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

      Mynda

      Reply
  3. Khem

    August 25, 2022 at 4:39 pm

    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.

    Reply
    • Mynda Treacy

      August 26, 2022 at 7:50 am

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

      Reply
  4. Rohan Sen

    July 19, 2022 at 6:17 pm

    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),”@”)))

    Reply
    • Mynda Treacy

      July 19, 2022 at 6:34 pm

      Hi Rohan,

      There’s no such function as ARRAYFORMULA in Excel.

      Mynda

      Reply
  5. NARAYAN

    April 25, 2022 at 3:40 pm

    =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,””))))

    Reply
    • Catalin Bombea

      April 25, 2022 at 6:01 pm

      Yes

      Reply
  6. Grant

    April 14, 2022 at 8:59 pm

    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.

    Reply
    • Mynda Treacy

      April 14, 2022 at 10:30 pm

      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.

      Reply
  7. Kenima

    March 18, 2022 at 4:39 am

    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")))))

    Reply
    • Mynda Treacy

      March 18, 2022 at 11:40 am

      Hi Kenima,

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

      =IF(AND(B1=1,C1=1,D1=1,E1=1)...

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

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

      Mynda

      Reply
  8. NAVEED UR REHMAN

    January 14, 2022 at 9:23 pm

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

    please correct

    Reply
    • Catalin Bombea

      January 15, 2022 at 12:04 am

      Hi Naveed,
      What is wrong?
      Or, how do you want it to work, if it’s not working as expected?

      Reply
  9. MANGALA MOHANTY

    November 20, 2021 at 11:40 pm

    =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

    Reply
    • Catalin Bombea

      November 21, 2021 at 2:03 am

      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.

      Reply
  10. Anup Verma

    August 19, 2021 at 3:02 pm

    =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

    Reply
    • Mynda Treacy

      August 19, 2021 at 4:25 pm

      Hi Anup,

      You haven’t used AND correctly. Try:

      =IF(D2=K2,"Q1 2022",IF(D2=H2,"Q2 2022",IF(D2=I2,"Q3 2022",IF(D2=J2,"Q4 2022",""))

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

      Mynda

      Reply
  11. dilshan

    July 28, 2021 at 3:44 pm

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

    pls send me correct one

    Reply
    • Mynda Treacy

      July 28, 2021 at 10:06 pm

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

      Reply
  12. Anu

    March 28, 2021 at 12:13 am

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

    I am not getting the answer

    Reply
    • Catalin Bombea

      March 28, 2021 at 2:43 am

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

      Reply
  13. Jotendar

    August 9, 2020 at 7:39 pm

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

    Reply
    • Catalin Bombea

      August 10, 2020 at 1:17 am

      Hi,
      Try:
      =MAX(5000,A1*0.25)

      Reply
  14. Tanvir Khan

    July 31, 2020 at 1:36 am

    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″)))

    Reply
    • Catalin Bombea

      July 31, 2020 at 2:28 am

      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.

      Reply
  15. Uma

    March 10, 2020 at 4:02 pm

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

    Reply
    • Catalin Bombea

      March 11, 2020 at 3:03 pm

      Hi Uma,
      Daily target should simply be: =B1/12/20

      Reply
  16. Mohammad Akbar

    January 13, 2020 at 6:47 pm

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

    Reply
    • Catalin Bombea

      January 14, 2020 at 7:50 pm

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

      Reply
  17. lina

    November 23, 2019 at 11:37 pm

    =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”))

    Reply
    • Mynda Treacy

      November 24, 2019 at 12:34 pm

      Did you have a question, Lina?

      Reply
  18. Dero

    September 19, 2019 at 8:44 am

    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?

    Reply
    • Mynda Treacy

      September 19, 2019 at 8:51 am

      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

      Reply
  19. Tiff

    March 22, 2019 at 1:47 pm

    =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 🙁

    Reply
    • Catalin Bombea

      March 22, 2019 at 9:24 pm

      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

      Reply
      • Tiffany upshaw

        June 17, 2019 at 11:32 pm

        Uploading now

        Reply
  20. Glenn

    February 5, 2019 at 5:14 am

    =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?

    Reply
    • Catalin Bombea

      February 5, 2019 at 2:10 pm

      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.

      Reply
  21. khalil billeh

    December 20, 2018 at 6:23 am

    =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%")))))))))))))))))))))))))))

    Reply
    • Philip Treacy

      December 20, 2018 at 10:05 am

      Hi Khalil,

      What is your question?

      Regards

      Phil

      Reply
  22. Joe

    December 18, 2018 at 6:36 am

    ‘=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?

    Reply
    • Mynda Treacy

      December 18, 2018 at 9:44 pm

      Hi Joe,

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

      =IF(C15="Tax Not Included",0,IF(and(C15="Tax on Material Only",sum(...

      Mynda

      Reply
  23. Chris Dollahite

    September 21, 2018 at 5:14 am

    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

    Reply
    • Mynda Treacy

      September 21, 2018 at 8:37 am

      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

      Reply
  24. Umar Khaled

    March 18, 2018 at 3:00 pm

    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?

    Reply
    • Mynda Treacy

      March 18, 2018 at 8:37 pm

      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

      Reply
  25. brandon

    February 13, 2018 at 5:54 am

    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.

    Reply
    • Mynda Treacy

      February 13, 2018 at 8:56 am

      Hi Brandon,

      Looks like too many double quotes. Try this:

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

      Double quotes return/specify text.

      Mynda

      Reply
  26. Robert Prather

    February 22, 2012 at 1:59 pm

    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?

    Reply
    • Mynda Treacy

      February 22, 2012 at 9:52 pm

      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.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.