• 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
    • 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel IF AND OR Functions Explained

You are here: Home / Excel Formulas / Excel IF AND OR Functions Explained
if or and if and formulas
October 2, 2010 by Mynda Treacy

Excel IF AND OR functions on their own aren’t very exciting, but mix them up with the IF Statement and you’ve got yourself a formula that’s much more powerful.

In this tutorial we’re going to take a look at the basics of the AND and OR functions and then put them to work with an IF Statement.  If you aren’t familiar with IF Statements, click here to read that tutorial first.

IF Formula Builder

IF Formula Builder

Our IF Formula Builder does the hard work of creating IF formulas.

You just need to enter a few pieces of information, and the workbook creates the formula for you.

AND Function

The AND function belongs to the logic family of formulas, along with IF, OR and a few others.  It’s useful when you have multiple conditions that must be met.

In Excel language on its own the AND formula reads like this:

=AND(logical1,[logical2]....)

Now to translate into English:

=AND(is condition 1 true, AND condition 2 true (add more conditions if you want)

OR Function

The OR function is useful when you are happy if one, OR another condition is met.

In Excel language on its own the OR formula reads like this:

=OR(logical1,[logical2]....)

Now to translate into English:

=OR(is condition 1 true, OR condition 2 true (add more conditions if you want)

See, I did say they weren’t very exciting, but let’s mix them up with IF and put AND and OR to work.

IF AND Formula

First let’s set the scene of our challenge for the IF, AND formula:

In our spreadsheet below we want to calculate a bonus to pay the children’s TV personalities listed.  The rules, as devised by my 4 year old son, are:

1)      If the TV personality is Popular AND

2)      If they earn less than $100k per year they get a 10% bonus (my 4 year old will write them an IOU, he’s good for it though).

Excel IF AND OR Functions IF AND example

In cell D2 we will enter our IF AND formula as follows:

In English first

=IF(Spider Man is Popular, AND he earns <$100k),  calculate his salary x 10%, if not put "Nil" in the cell)

Now in Excel’s language:

=IF(AND(B2="Yes",C2<100),C2x$H$1,"Nil")

You’ll notice that the two conditions are typed in first, and then the outcomes are entered.  You can have more than two conditions; in fact you can have up to 30 by simply separating each condition with a comma (see warning below about going overboard with this though).

IF OR Formula

Again let’s set the scene of our challenge for the IF, OR formula:

The revised rules, as devised by my 4 year old son, are:

1)      If the TV personality is Popular OR

2)      If they earn less than $100k per year they get a 10% bonus.

IF OR Formula

In cell D2 we will enter our IF OR formula as follows:

In English first

=IF(Spider Man is Popular, OR he earns <$100k), calculate his salary x 10%, if not put “Nil” in the cell)

Now in Excel’s language:

=IF(OR(B2="Yes",C2<100),C2x$H$1,"Nil")

Notice how a subtle change from the AND function to the OR function has a significant impact on the bonus figure.

Just like the AND function, you can have up to 30 OR conditions nested in the one formula, again just separate each condition with a comma.

Try other operators

You can set your conditions to test for specific text, as I have done in this example with B2="Yes", just put the text you want to check between inverted comas “   ”.

Alternatively you can test for a number and because the AND and OR functions belong to the logic family, you can employ different tests other than the less than (<) operator used in the examples above.

Other operators you could use are:

  • =             Equal to
  • >             Greater Than
  • <=           Less than or equal to
  • >=           Greater than or equal to
  • <>           Less than or greater than

Warning: Don’t go overboard with nesting IF, AND, and OR's, as it will be painful to decipher if you or someone else ever needs to update the formula in months or years to come.

Note: These formulas work in all versions of Excel, however versions pre Excel 2007 are limited to 7 nested IF’s.

Download the Workbook

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

You can download the workbook used in this example to practice here. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Excel IF AND OR Practice Questions

IF AND Formula Practice

In the embedded Excel workbook below insert a formula (in the grey cells in column E), that returns the text 'Yes', when a product SKU should be reordered, based on the following criteria:

  1. If Stock on hand is less than 20,000 AND
  2. Demand level is 'High'

If the above conditions are met, return 'Yes', otherwise, return 'No'.

Tips for working with the embedded workbook:

  1. Use arrow keys to move around the worksheet when you can't click on the cells with your mouse
  2. Use shortcut keys CTRL+C to copy and CTRL+V to paste
  3. Don't forget to absolute cell references where applicable
  4. Do not enter anything in column F
  5. Double click to edit a cell
  6. Refresh the page to reset the embedded workbook

IF OR Formula Practice

In the embedded Excel workbook below insert a formula (in the grey cells in column E) that calculates the bonus due for each salesperson. A $500 bonus is paid if a salesperson meets either target in cells C24 and C25, otherwise they earn $0 bonus.

Want More Excel Formulas

Why not visit our list of Excel formulas. You'll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy 🙂

if or and if and formulas

More Excel Formulas Posts

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 formulas

Excel Formulas

Here you'll find a list of common Microsoft Excel formulas and functions explained in plain English, and applied to real life examples.
IFERROR Function

Excel’s IFERROR Puts an End to Messy Workarounds

Excel's new function; IFERROR will improve the speed of your formulas and reduce workbook file sizes. I don't know how we lived without it.
sumif and sumifs formulas

Excel SUMIF and SUMIFS Formulas Explained

How to write Excel SUMIF and SUMIFS function, including examples and download workbook.

More Excel Formulas Posts

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.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: excel formulas
Previous Post:Excel Nested IFsExcel Nested IF’s Explained
Next Post:Excel SUBTOTAL Formula Explained

Reader Interactions

Comments

  1. Aymen T.

    January 20, 2023 at 7:59 pm

    Hell Sir I am working on a sheet (data given below). I have a time and want to check if it falls between a given time range or not. The formula i am using is “If(AND(C72>A72,C72<B72), "TRUE", "FALSE")". Column C is extracting value from another timestamp column. It works (Column D) if I add the value manually in Column C but it doesnt work on automatically extracted value. Please help!
    A B C D
    11:33:48 11:53:48 12:50 FALSE
    12:20:22 12:38:21 12:21 TRUE
    12:39:27 13:00:09 12:40 TRUE
    14:16:23 14:20:49 14:17 TRUE
    14:20:54 14:22:56 14:21 TRUE
    4:00:00 16:00:00 12:00:00 TRUE

    Reply
    • Catalin Bombea

      January 20, 2023 at 8:32 pm

      Hi Aymen,
      How are you extracting the values automatically?
      Normally, if calculation is set to Automatic, formulas are recalculated when the source cells change.

      Reply
      • Aymen Tasneem

        January 23, 2023 at 4:06 pm

        I am taking the timestamp if certain column is filled, formula is “IF(H36″”,IF(U36″”,U36,NOW()),””)”

        Reply
        • Catalin Bombea

          January 24, 2023 at 2:27 am

          Can you please provide a sample file that replicates the problem on our forum? (create a new topic)

          Reply
  2. Joy

    September 24, 2022 at 2:53 am

    I need your help in one topic.
    As the Sumifs function does the different criteria with Different range. I need the same in IF condition Kindly help.

    For instance
    Joy is the employee and he does three task need to check the condition in every task he must have quality of above 98%.
    If quality is less than 98% in any of the three task it must return value as Quality is Low for all three columns else if quality is more than 98% for all need to return as Selected in all three columns.

    Consider below as given in excel sheet

    Name task quality Remarks
    Joy. Adhoc. 99.
    Joy. Pinning. 97.
    Joy. Tagging. 100.

    Kindly please help me with the condition

    Reply
    • Philip Treacy

      September 25, 2022 at 11:55 am

      Hi Joy,

      if your Quality column is D3:D5 then try this

      =IF((COUNT($D$3:$D$5)/COUNTIF($D$3:$D$5,”>98″))=1,”Selected”,”Quality is low”)

      Note that you haven’t said what do do when the Quality score equals 98.

      If you are still stuck please start a topic on our forum and attach your example file with data.

      Regards

      Phil

      Reply
      • Michael Cruz

        January 5, 2023 at 5:29 pm

        Hi there! I just wanna seek help with this problem I encounter regarding prevention of duplicate entries. Here’s the scenario if the “couple with the same name and same birthdate that encoded already” must prevent to be encoded again as it will be a duplication. Is it possible that two rows will and 2 columns will be the criteria? Thanks for the help

        Reply
        • Catalin Bombea

          January 5, 2023 at 7:39 pm

          Hi Michael,
          Multiple columns can be used as a unique key, but not multiple rows, that is unusual. You should review the way you store information, a unique record should not be split into multiple rows.

          Reply
          • Michael Cruz

            January 6, 2023 at 3:17 pm

            Noted with this, Thanks!

  3. Paygan

    September 20, 2022 at 8:04 am

    Hi Everyone.
    Just looking to see, if anyone could help me under stand what this formula is and the meaning behind it =IF(D7>0,0.7,0) im new to IF functions and slowly learning.

    ive notice a friend has used this IF Function =IF(D7>0,0.7,0) to calculate milage.

    can anyone explain to me how it works, so im able to create one myself, but so i fully understand the meaning behind it.

    Thank yous all

    Reply
    • Mynda Treacy

      September 20, 2022 at 9:10 am

      Hi Paygan, in English that formula reads:

      If the value in cell D7 is greater than 0, return 0.7, otherwise return 0.

      Mynda

      Reply
  4. Stanley Prentice

    July 20, 2022 at 6:07 am

    =IFS(B15=””,D15=””,B15>0,J$10. Sorry, the previous had the wrong formula. This is the correct one however the rest of the previous post was correct. Does anyone have an answer?

    Reply
    • Mynda Treacy

      July 20, 2022 at 2:15 pm

      Hi Stanley,

      The IFS function syntax is logical test, result, logical test, result…

      Please see the IFS function tutorial. 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.

      Reply
  5. Stanley Prentice

    July 20, 2022 at 5:55 am

    =IFS(B15=””,D15=””)+OR(B15″”,D15=J6) This formula was written in cell J19. I want the results to be placed in cell D15 not in cell J19. the word in cell J6 is: Pension. I have other words in col. J that I may want to use depending on Dxx=Jxx. with xx being the cell number

    Reply
  6. Prem Aradhya

    July 4, 2022 at 4:18 am

    Could you suggest me a formula for scoring from 0 10, for the percentage of marks from 35 to 100.
    Ex. For 35% the score is 0 and 100% the score is 10.

    Reply
    • Mynda Treacy

      July 4, 2022 at 9:17 am

      Hi Prem, please use this VLOOKUP on a sorted list technique.

      Reply
  7. Naweed

    May 18, 2022 at 5:48 pm

    Hi Guys,

    can anybody help me out for below amount distribution?

    if the bases is 2300 how to divide 100% on different 4 amounts
    1. 2250
    2. 1760
    3. 1250
    4. 1240

    what amount percentage will be applied on each from 2300?

    thank you

    Reply
    • Mynda Treacy

      May 19, 2022 at 2:24 pm

      Hi Naweed,

      I’m not sure what you mean, sorry. Pease post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  8. Galaxy Eduaction

    February 17, 2022 at 8:17 pm

    Thanks for sharing this is aweesome.

    Reply
    • Mynda Treacy

      February 17, 2022 at 8:19 pm

      Our pleasure 🙂

      Reply
  9. kayane kumarasamy

    January 26, 2022 at 1:05 am

    Hi i have an excel project and really struggling with this question. No idea which function to use to label specific words: please see question below.

    Use IF, data cleaning and / or lookup formulae to populate the columns for Type, Category and Detail.
    >> Type: Codes beginning with “R” should be labelled “Revenue”. Codes beginning with “E” should be labelled “Expense”.

    Exampes of codes ” EDA7001″ , RNC2001

    >> Category: refer to the Lookups worksheet for the Categories.
    >> Detail: refer to the Lookups worksheet for the Detail.

    Reply
    • Philip Treacy

      January 26, 2022 at 9:18 am

      Hi Kayane,

      You should post this kind of question in our forum along with the workbook that contains the data, because I can’t show you how to do the lookups without your data.

      For the Type, you can use string functions:

      =IF(LEFT(A1,1)="E","Expense")
      
      =IF(LEFT(A1,1)="R","Revenue")

      but if there are only two Types you can use a single IF function for both

      =IF(LEFT(A1,1)="E","Expense","Revenue")

      For the lookups you could use either VLOOKUP or XLOOKUP

      Regards

      Phil

      Reply
  10. James

    September 17, 2021 at 6:08 am

    Hi all,
    I have 5 students – Adam, Oliver, George, Harry, Jack, Jacob – in cells A2, B2, C2, D2, E2, F2
    and I have their grades 4, 4, 5, 6, 7, 8 – in cells A3, B3, C3, D3, E3, F3.

    I am trying to figure out a formula for the following
    1. To give me the lowest grade (i.e. 4) in cell G3 and the respective student’s name in cell H3.
    2. To give me the 2nd lowest grade (i.e. 5) in cell I3 and the respective student’s name in cell J3.

    Any help would be more than highly appreciated.

    Reply
    • Mynda Treacy

      September 17, 2021 at 8:58 am

      Hi James,

      You can use the SMALL function, to find the 2nd lowest grade and then use INDEX & MATCH to lookup the corresponding name, but it’s not clear what you want to do in the event of a tie. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  11. Vic

    May 11, 2021 at 6:08 am

    Hey all. I am trying to figure out a formula for the following. I have 14 variables of data. If a cell equals a certain number, it will populate with that text. if not, it will show up as false. What I would like to do now, is if the range of X:X = false, look for other text and put that text into another cell. If its:
    A1 False B1 text via formula needed in A5
    A2 Text
    A3 False
    A4 False
    A5 =formula for what i’m looking for.

    I want it to look at the 4 cells and determine what text isn’t false and put it in a different cell. Something like If A1:A4=False, then B1=1 of 14 random texts.
    Any help is appreciated! Thanks

    Reply
    • Catalin Bombea

      May 11, 2021 at 2:14 pm

      Hi Vic,
      How can the formula decide which one of the 14 texts should be displayed? Can you describe all conditions (assuming that each of those 14 texts should be displayed when a specific condition is met)?
      If there is no rule and one of 14 texts should be randomly displayed:
      =IF(COUNTIF(A1:A4,”False”)=4,INDEX({“Date_GR”,”What_GR”,”Where_GR”,”What purchase_GR”,”Tip_GR”,”Sub_total_GR”,”Total_Cost_GR”,”Remarks”,”Where_GR”,”What purchase_GR”,”Tip_GR”,”Sub_total_GR”,”random text 1″,”random text 2″},RANDBETWEEN(1,14)),””)

      Reply
      • Vic

        May 12, 2021 at 12:13 am

        Catalin, thank you for your response. I am only going to use 5 texts for example. Okay, I have the following table:
        A B C D
        1 Txt1 3 Txt3
        2 Txt2
        3 Txt3
        4 Txt4
        5 Txt5

        When the data shows a number 1-5, it displays the corresponding text.

        Since I have 5 values, I have a line for each option.

        If the formula is false, it displays false.

        Id like to find a formula that selects the range and selects the line automatically that does not read “False”, and displays that text in another cell

        A B C D
        MODEL# 1 1 Txt1
        WANT NAME DISPLAYED HERE: XXXX 2 Txt2
        =IF($B$1=$CF$1, “Txt1”) 3 Txt3
        =IF($B$2=$CF$2, “Txt2”) 4 Txt4
        =IF($B$3=$CF$3, “Txt3”) 5 Txt5
        =IF($B$4=$CF$4, “Txt4”)
        =IF($B$5=$CF$5, “Txt5”)

        Now, I want to take B3:B7 and if #2 is the model number, B4 will read Txt2 and B3,B5,B6,&B7 will read FALSE. I want a formula to look through B3:B7 pick the text that isn’t “FALSE” and display that text in cell B2.

        Does this make sense? Please let me know if you have any more questions. Thanks!

        Reply
        • Catalin Bombea

          May 12, 2021 at 3:11 am

          Can you please upload a sample file with your data and the expected results? will be much easier to help you.
          Use our forum to create a new topic and upload a sample file.

          Reply
          • Vic

            May 12, 2021 at 5:44 am

            I tried registering, but when I went to log in, it will not let me.

          • Mynda Treacy

            May 12, 2021 at 9:13 am

            Hi Vic, please reach out via email (website at MyOnlineTrainingHub.com) so we can help you troubleshoot. What I can say here is the email address you used for this comment does not have an account set up in our system, so I suspect you haven’t registered properly. Mynda

          • Vic Trolio

            May 12, 2021 at 11:44 pm

            I successfully registered and posted a sample file in the forum. Title of the topic is: IF and OR Function Help Needed.

            Thanks!

  12. anvitha

    March 6, 2021 at 10:39 pm

    hey, I want a function to solve this problem

    Customers who want to avail of an Early Bird discount must place their orders on weekdays between 11:45 a.m. – 12:00 p.m. (for lunch) or 6:45 p.m. – 7:30 p.m. (for dinner).
    column D has delivery day(number) details and column E has delivery timings (12-hour format)

    please help me solve this. thanks in advance

    Reply
    • Catalin Bombea

      March 7, 2021 at 4:05 am

      Hi,
      Can you please upload a sample file with an example of expected results? Will be much easier for us to understand your situation and help you.
      Create a new topic on our forum after you sign up to upload the sample file.

      Reply
  13. Brian Coombs

    March 3, 2021 at 12:31 pm

    Hi,
    I am trying to do a formula involving multiple “IF” for the same cell, I have 4 possible answers for the same cell with different outcomes for each, so if (A1) is 0:051(or)0:151 0:2.51.
    I have tried the nested ifs from the tutorials and the help in excel, and the “and” “or” but I just can’t seem to get it to give me an increase in the “value” cell…need help if you can please.

    Reply
    • Brian Coombs

      March 3, 2021 at 12:38 pm

      If A1 [is less than ?? in cell A2] then A3 “=” +3 or if A1 is greater than?? but less than?? [in cell A2] then A3 “=”+4….etc for all four variations

      Reply
      • Philip Treacy

        March 3, 2021 at 2:01 pm

        Hi Brian,

        I’m afraid the problem isn’t quite clear. What kind of value is 0:2.51? Text? But you are trying to check if a value is greater or less than something so you must be working with numbers?

        Also with this part if A1 is greater than?? but less than?? [in cell A2] if you have a value in A2 then you can’t check if something else is greater than and less than it. It can only be one or the other.

        Please start a topic on our forum and attach the file you are working with so we can understand things better.

        Regards

        Phil

        Reply
  14. NAVAJITH.N

    February 18, 2021 at 5:43 pm

    IF THE SALES IS LESS THAN RS.10001,THE COMMISSION IS 10% OTHERWISE YOU WILL GET 11% COMMISSION FOR THE AMOUNT ABOVE RS.10000. Please answer my question and give if formula.

    Reply
    • Mynda Treacy

      February 18, 2021 at 7:40 pm

      It’s difficult to give you a formula without cell references for your source data. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  15. Flower

    February 17, 2021 at 7:25 am

    Hi… I am trying to work out a formula for a pricing excercise at work and am very stuck.
    This is the information I have…

    Base cost to service up to 3 fire extinguishers is $45
    Cost to service any additional fire extinguisher is $4.05

    Trying to set up a formula that shows this.
    For example…
    # of extinguishers Total Service cost
    3 $40.00
    4 $44.05 (base cost of $40.00 + 4.05 for the additional unit)
    5 $48.10 (base cost of $40.00 + 8.10 for the additional units)

    Are you able to help set this up?

    Reply
    • Mynda Treacy

      February 17, 2021 at 10:34 am

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

      Reply
  16. Ana

    February 13, 2021 at 1:36 am

    HELP :,(
    3. The seniority bonus is calculated at the employment salary and is determined according to the seniority in work as follows:
     pt. seniority 20 years – 25%

    Reply
    • Mynda Treacy

      February 13, 2021 at 2:12 pm

      This looks like a homework question, Ana. We don’t do homework, but if you have a go at writing the formula yourself then we can help you if you get stuck.

      Reply
  17. Philisha

    January 12, 2021 at 7:24 am

    Can you tell me the formula to use for:

    If J1 is $10,000 or more put a “2” in cell B1
    If J1 is less than $10,000 put a “1” in Cell B1

    Thank you

    Reply
    • Catalin Bombea

      January 12, 2021 at 2:07 pm

      Hi Philisha,
      Try this formula in cell B1:
      =IF(J1>=10000,2,1)

      Reply
  18. John Lim

    January 2, 2021 at 12:23 am

    Hi all, Happy New year. can you help me on this, i want to display the monthly due dates automatically in each column. Example the contract is 6 months and the start date is Jan.31, 2021. and the due date is every 31st day of the month, So when i input 12 months, automatically the 12 columns will display the due dates of each month.

    Column A – January 31, 2021
    Column B – February28, 2021
    Column C – March 31, 2021
    Column D – April 30, 2021
    Column E – May 31, 2021
    Column F – June 30, 2021

    Thank you

    Reply
    • Catalin Bombea

      January 2, 2021 at 4:07 pm

      Hi John,
      Use this in the first cell:
      =IF(Column(A1)>B1,””,EOMONTH(A1,Column(A1)))
      Drag it to the right.
      in A1 there should be the start date, in B1 the number of months.

      Reply
  19. John Lim

    December 23, 2020 at 1:33 am

    Hi everyone, i need help….

    D5 = Tenant
    E5 = monthly rental payment ( P 10,000.00)
    F5 = +VAT 12%
    G5 = – EWT 5%
    H5 = PEZA accredited

    i want the result that if the tenant is PEZA accredited there is no additional of 12% VAT

    is it possible to add the current formula?

    Current Formula =(E5+F5)-G5

    thanks

    Reply
    • Catalin Bombea

      December 23, 2020 at 3:15 am

      Hi John,
      Instead of F5, put a condition:
      =(E5+IF(H5=”PEZA accredited”,0,F5))-G5

      Reply
      • John Lim

        December 23, 2020 at 2:21 pm

        thank you Ms. Catalin Bombea

        and also ask for your assistance on how can i monitor aging request.

        Example i requested a report to be summited but i want to reflect that requested report is 3 days ago pending and also i want that the font text should be highlighted in red font color for the entire row

        A5 = date request
        B5 = Daily Time Record (Report status)
        C5 = Remarks

        thank you

        Reply
        • Catalin Bombea

          December 23, 2020 at 4:55 pm

          Hi John,
          Can you please upload a sample file on our forum? will be much easier to help you on your real data structure.
          Thank you

          Reply
  20. Priya

    December 20, 2020 at 6:59 pm

    Hi, i need a help to findout the formula for the below condition.

    here there are multiple columns, the first column will have the “A,B,C..etc..” and the 2nd column represent the value for each row. here if i enter the text in the “Text” column then the respective value should display in the Value column.

    For Ex:
    if i enter A then the value should be “1”
    if i enter AB then the value should be “!2”
    if i enter ACH then the value should be “138”

    Heading1 Heading1 Text1 Value
    A 1
    B 2
    C 3
    D 4
    E 5

    kindly help!

    Reply
    • Mynda Treacy

      December 20, 2020 at 8:52 pm

      Hi Priya,

      You should use a lookup table for the values and then you can use VLOOKUP or XLOOKUP (Microsoft 365) to return the value for the letter entered.

      Mynda

      Reply
      • Priya

        December 20, 2020 at 9:08 pm

        Can you please help me with the query this would help me

        Reply
        • Mynda Treacy

          December 20, 2020 at 9:14 pm

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

          Reply
          • Priya

            December 20, 2020 at 9:48 pm

            i couldnt upload the excel file. but this is the formula i used for one value,

            =IFERROR(VLOOKUP(D4,$B$4:$C$13,2,FALSE),””)

            Code Number Serial Rate
            A 0 E 4 (i used the above formula)
            B 1
            C 2
            D 3
            E 4
            F 5
            G 6
            H 7
            I 8
            J 9

            if i choose “AA” then 00 should display, if i choose “BCA” 120 should display, if i choose “A” then 0 should display, if i choose “ABCDEFGHIJ” then 0123456789 should display. kindly help!

          • Mynda Treacy

            December 21, 2020 at 8:49 am

            Hi Priya, please get in touch via email with the error you get when uploading the file. I can’t help you here in the comments. The forum is the best place for support like this where an example file is required.

            Mynda

  21. sree

    December 8, 2020 at 5:35 pm

    Hi

    I need a formula in excel like, if the amount in C6 is 10000 and less the product should be 2.36, if it is 10001 and less than 100000 it should be 4.72, if it is 100001 and less than 200000 it should be 14.16.

    Pls help

    Reply
    • Catalin Bombea

      December 8, 2020 at 8:43 pm

      Hi,
      Try this one:
      =INDEX({2.36,4.72,14.16,0},MATCH(A1,{0,10001,100001,200001},1))
      Catalin

      Reply
  22. TRUSHAR

    November 22, 2020 at 2:02 am

    PLEASE HELP ME TO SET FORMULA WHERE ONE CELL VALUE IS FIXED SAY 1000 AND I WANT TO SET FORMULA WHICH WILL CALCULATE AUTOMATICALY RANGE IN OTHER 2 CELL FRM FROM THE BASE PRICE 15%-20% & 25% TO 33%. EXAMPLE: 1000(B1 CELL VALUE) CALCULATE RESULT IN C1 CELL (1150-1200) AND IN D1 VALUE (1250-1330).

    Reply
    • Philip Treacy

      November 22, 2020 at 11:34 am

      Hiya,

      Sorry the problem isn’t clear. Can you please start a topic on our forum and attach some sample data with an example of your expected results.

      Regards

      Phil

      Reply
  23. sabeen

    November 12, 2020 at 11:40 pm

    ITEM NO DESCRIPTION QTY UNIT PRICE DISCOUNTED UNIT PRICE
    5101 330ML (40 PC BOX) 300 13.00 9.50

    =IF(C21=0,”0.00″,IF(AND(C2149,C21100,C21<301),"9.50",9))))

    2 criteria checking.
    i want one extra criteria also check it,

    1) if ITEM=5102 and if QTY
    (C21=0,"0.00",IF(AND(C2149,C21100,C21<301),"9.50",9))))
    2)if ITEM=5100 and if QTY
    (C21=0,"0.00",IF(AND(C2149,C21100,C21<301),"2.50",2))))

    Reply
    • Mynda Treacy

      November 13, 2020 at 12:50 pm

      Hi Sabeen,

      Aside from adding the extra criteria, there are some issues with your formula that you may not be aware of. i.e.

      1. =IF(C21=0,”0.00″ this will return text in the form of 0.00 not a zero. I presume you actually want a zero. Likewise for “9.50” should be 9.50 and “2.50” should be 2.50.
      2. AND(C2149,C21100,C21<301), is only testing if C21 is less than 301. C2149 and C21100 have no logical test. I presume you want to test all 3 cells are < 301.

      Assuming the above, then you can use this formula:

      =IF(C21=0,0,IF(AND(cell containing the item=5102,C2149<301,C21100<301,C21<301),9.50,IF(C21=0,0,IF(AND(cell containing the item=5100,C2149<301,C21100<301,C21<301),2.50,9))))

      Mynda

      Reply
  24. Jem

    November 5, 2020 at 1:33 pm

    Hi there, Please help me with the correct formula for this question;

    If the invoice is paid in full as of September 30,2020, display Yes; otherwise display NO

    Reply
    • Mynda Treacy

      November 5, 2020 at 2:13 pm

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

      Reply
  25. Shaylen

    October 14, 2020 at 2:49 am

    Hi there im struggling with a question in an excel assignment, so it goes like this.
    “The delivery date should be within 10 days from today expect if the invoice is created in December, then we need 21 days time, hint use an IF statement”

    I’ve tried doing the IF statement but it’s just not working, I’ve done.

    =IF(TODAY()=”December”, Today()+21,Today()+10)

    It does add on 10 days since we not in December, but if i change today’s date to December it still adds on 10 days

    Reply
    • Mynda Treacy

      October 14, 2020 at 10:18 am

      Hi Shaylen, the question isn’t clear to me, but looking at your formula, TODAY returns a date, not a month name. If you want to check if the month is the 12th, you’d do this:

      =IF(MONTH(TODAY())=12, Today()+21,Today()+10)

      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.

      Mynda

      Reply
  26. Sayali

    August 25, 2020 at 10:32 pm

    hi I need help for below formula,need to set the “GTS IS….” part mapped as per the E54 col.input
    =IF(AND(OR(E54=”100″,E54=”101″,E54=”104″,E54=”105″,E54)),” GTS IS:New logo”, IF(AND(OR(E54=”102″,E54=”103″,E54=”106″,E54=”107″,E54=”108″,E54=”109″,E54)),”GTS IS:Project /RFS” , IF(AND(OR(E54=”213″,E54=”214″,E54=”215″,E54=”217″,E54=”218″,E54=”222″,E54=”223″,E54=”301″,E54=”302″,E54=”303″,E54=”300″,E54)),”GTS IS:Other”,IF(AND(OR(H54=”211″,E54),(H54=”216″,E54)),”GTS IS:Skills for Value”,”GTS IS:Center to Center”))))

    Reply
    • Mynda Treacy

      August 26, 2020 at 9:16 am

      Hi Sayali,

      I think it would be better to use VLOOKUP or XLOOKUP if you have Microsoft 365/Office 365. You can have a list of the categories (GTS IS:New logo etc.) in a column beside the numbers they match. See links for more detailed tutorials. If you’re still stuck, please post your question and a sample Excel file on our forum where we can help you further.

      Mynda

      Reply
  27. So

    August 17, 2020 at 1:14 pm

    hi
    i need a help with avarege data row , that gived number 1-4 and 1 is 50%, 2 is 75%, 3 is 85%, 4 is 100%
    =AVERAGE(H4:AG4,(IF(AND(H4:AG4=4),100,IF(AND(H4:AG4=3),85,IF(AND(H4:AG4=2),75,IF(AND(H4:AG4=1),50))))))

    Reply
    • Mynda Treacy

      August 17, 2020 at 3:23 pm

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

      Reply
  28. Terri P

    July 30, 2020 at 6:54 am

    I need help with adding a formula to an existing MOD formula in a timesheet. I have in column B, Time IN, in column C is Time OUT and column D is Project Hours. The formula I have in cell D2 is =MOD(C2-B2,1). Cells B2 & C2 are formatted as h:mm AM/PM and cell D2 is formatted as h:mm.

    If I type 9:00 am in cell B2, and leave cell C2 blank, it shows 15:00 in cell D2 for the Project Hours. I know this is because the formula is based on time and it is showing how many hours between 9:00 am and 12:00 midnight. We have some employees who clock in at 11:00 pm and will clock out after midnight, which is why I have the MOD formula.

    Eventually, the employee will type in their Time OUT in cell C2, but until they do so, I don’t want D2 to show 15:00 hours, instead I would like for cell D2 to be blank until cell C2 is typed in. Is there a formula for this that can be included with the existing MOD formula or even a conditional format set?

    Thank you.

    Reply
    • Mynda Treacy

      July 30, 2020 at 8:51 am

      Hi Terri,

      =IF(C2=””,””,MOD(C2-B2,1))

      Mynda

      Reply
      • Terri P

        July 30, 2020 at 9:24 am

        Thank you so much Mynda, that worked!! Have a great day!

        Terri

        Reply
  29. Jonathan A. Del Rosario

    July 29, 2020 at 3:27 pm

    Please help me make a formula for my data, scenario is this:
    Where column B2 is the Release Date and column B5 is the 1st Amort Date.
    f the release date (B2) Range from 1 – 25 of the month the 1st Amort Date (B5) should be the 10th of the following Month,
    then if the release date (B2) Range from 26 – 31 of the month the 1st Amort Date (B5) should be the 5th of the following Next Month.

    Example: B2 – July 15, 2020
    B5 – August 10, 2020

    then
    B2 – July 26, 2020
    B5 – September 5, 2020

    Reply
    • Mynda Treacy

      July 30, 2020 at 8:57 am

      Hi Jonathan,

      Try this:

      =IF(DAY(B2)<26,DATE(YEAR(B2),MONTH(B2)+1,10),DATE(YEAR(B2),MONTH(B2)+1,5))

      Mynda

      Reply
      • Jonathan A. Del Rosario

        July 30, 2020 at 1:04 pm

        Thanks for the help..I will try it..

        Reply
      • Jonathan A. Del Rosario

        July 30, 2020 at 1:15 pm

        Thank you very much Mynda!! Just try it and got it exactly what I want.

        Reply
        • Mynda Treacy

          July 30, 2020 at 1:19 pm

          Glad I could help 🙂

          Reply
  30. Dev P

    July 27, 2020 at 8:12 pm

    Hi,

    I’m trying to calculate Surcharge where condition should be like this. If C18 is > 50,000,000 then surcharge is 10%, if C18 is >10,000,000 then surcharge is 20%, if C18 is >20,000,000 the surcharge is 30%. But unfortunately, when calculating surcharge value is still 10% even if this amount is more than 10,000,000

    Rounded Total taxable income (C18) 10,000,000.00
    Total Tax (C20) 2,812,500.00 =ROUND(IF(C18>1000000,(C18-1000000)*30%+112500,IF(C18>500000,(C18-500000)*20%+12500,IF(C18>250000,(C18-250000)*5%,0))),)

    Surcharge (C21) 281,250.00 =IF(C18>=5000000,C20*10%,IF(C18>=10000000,C20*15%,IF(C18>=20000000,C20*25%,1)))

    Please help.

    Reply
    • Philip Treacy

      July 27, 2020 at 8:30 pm

      Hi Dev,

      IF stops evaluating when it gets a True result and your first test says is C18 >= 5 000 000, which it is. So you get C20*10%.

      You need to reorder the tests like this

      =IF(C18>=20000000,C20*25%,IF(C18>=10000000,C20*15%,IF(C18>=5000000,C20*10%,1)))

      Regards

      Phil

      Reply
      • Dev P

        July 28, 2020 at 6:45 pm

        Hi Phil,

        Working perfectly. Thanks a lot. 🙂

        Reply
        • Philip Treacy

          July 29, 2020 at 9:16 am

          no worries.

          Reply
  31. Liz M

    June 12, 2020 at 8:41 am

    I’m trying to work out the costs of a booking depending on hours booked against different prices. I’m not sure about the IFs function and tried nested Ifs but I can’t get it to work. I need the formula to count three time ranges and then multiply the count by a price. Thank you in advance.

    =IFs((F9:I9=”8.30 – 15.30″,COUNTIF(F9:I9,”8.30 – 15.30″)*28),+ (IF(F9:I9=”8.00 – 17.00″),COUNTIF(F9:I9,”8.00 – 17.00″)*35) + (If(F9:I9=”8.00 – 18.00″,countif(F9:I9,”8.00 – 18.00″)*38.5)))

    Reply
    • Philip Treacy

      June 12, 2020 at 10:11 am

      Hi Liz,

      Try this

      =COUNTIF(F9:I9,"8.30 - 15.30")*28 + COUNTIF(F9:I9,"8.00 - 17.00")*35 + COUNTIF(F9:I9,"8.00 - 18.00")*38.5

      Regards

      Phil

      Reply
      • Liz

        June 13, 2020 at 5:15 am

        Thank you, that’s brilliant and it totally worked.

        Reply
        • Philip Treacy

          June 13, 2020 at 9:21 am

          You’re welcome.

          Reply
  32. Sumit Singh Chaudhary

    June 11, 2020 at 1:37 pm

    We would like to see which of our orders are generating the most revenue. If an order has an Order Total of more than $1,000, we will call this a “Star” order.

    Populate column AG: Star Order Proposal A with the value “Star” if the Order Total is more than $1,000. If it is not, the cell should simply remain blank. How many “Star” orders are there?

    Reply
    • Philip Treacy

      June 11, 2020 at 2:40 pm

      Hi Sumit,

      Without your data it makes it difficult to write the formulae for you. You can post a topic on our forum and attach your file.

      However, you could try

      =IF(A1>1000,”Star”,””)

      You should consider using Conditional Formatting to visually indicate your Star orders by, for example, filling the cell with a colour.

      In order to count how many Star orders you have, you can use COUNTA e.g.

      =COUNTA(A1:A5)

      Regards

      Phil

      Reply
  33. suzu

    June 4, 2020 at 2:43 am

    how to make formula in this condition after 8:30 and before 16:30, fill the cell color with red

    Reply
    • Catalin Bombea

      June 4, 2020 at 7:38 pm

      You should use:
      =AND($B2>=8.5/24, $B2<=16.5/24) Adjust the starting cell range for conditional formatting as needed. I assume you have time values in the range.

      Reply
  34. mohammed ab Sheriff

    May 29, 2020 at 3:16 am

    A B C D E F
    1 FIRST NAME SURNAME POSITION FULL NAME AGE ANNUAL SALARY
    2 JOHN SMITH MANAGER 35 85
    3 JIMMY MICHAELS SALESPERSON 26 60
    4 MICHELLE LEVINE RECEPTIONIST 37 55
    5 LEVI JOHNSON TECHNICIAN 40 75
    6 JENNA MASTERSON PROGRAMMER 32 100

    IF Age of a worker is >= 35, then ALLOWANCE should be 15% of his/her age, else allowance should be 9% of his or her age

    Reply
    • Mynda Treacy

      May 29, 2020 at 8:47 am

      Hi Mohammed,

      This is a simple IF formula e.g.:

      =IF(age>=35,age*.15,age*.09)

      Mynda

      Reply
  35. Tonyo Godinho

    May 10, 2020 at 7:24 am

    If J3>K3 Sell and If J3<K3 Buy

    Reply
    • Mynda Treacy

      May 11, 2020 at 2:08 pm

      Hi Tonyo,

      =IF(J3>K3, “Sell”, “Buy”)

      Mynda

      Reply
  36. Hlobsile

    May 4, 2020 at 6:05 pm

    Hello

    Can you please help me, I don’t understand more about AND and OR. can you please explain and justify this for me: =AND(E2=”Married”,F2=1)*1

    Reply
    • Mynda Treacy

      May 4, 2020 at 6:57 pm

      The AND part of this formula: =AND(E2=”Married”,F2=1) returns a TRUE or FALSE. When you perform a math operation on TRUE or FALSE, like in your example, *1, it converts TRUE and FALSE to their numeric equivalents of 1 and 0 respectively, so the result of the formula will always be 0 or 1, depending on whether both logical tests are TRUE or not. I hope that helps.

      Mynda

      Reply
  37. jennifer michaels

    May 2, 2020 at 12:29 am

    Hi, i know could you help me understand what this says? also, direct me a page that may explain max & mins? this page was very helpful for the If statements.

    MAX(0,MIN((B9-IF(B10=”Y”,B5,0))*B13,B7))

    thank you!

    Reply
    • Catalin Bombea

      May 2, 2020 at 6:44 am

      Hi Jennifer,
      Without context and real practical use, they mean nothing.
      There are 2 parts, MIN is used here to put a limitation, the result should not be higher than the limit set in B7, BUT can be lower : MIN( (B9-IF(B10=”Y”,B5,0))*B13, B7)
      Then, the result has another condition applied: the result of the MIN function should not be negative, in this case MAX(0,negative number) will always be 0. If MIN returns a positive value, it will obviously be the value returns by MAX calculation.
      =MAX(0, resultFrom MIN function )

      Reply
  38. Grace

    April 30, 2020 at 9:33 am

    I am not very savvy on Excel and I am trying to figure out a macro that is in my spreadsheet. =IF(D2=””,””,IF(D2=E2,”Pass”,”Check”))

    Reply
    • Mynda Treacy

      April 30, 2020 at 11:58 am

      Hi Grace,

      In English, your formula reads:

      If D2 is empty, then return nothing, if the value in D2 is the same as the value in E2, then enter the text “Pass”, otherwise enter the text “Check”.

      I hope that helps.

      Mynda

      Reply
  39. Kuba

    April 24, 2020 at 2:10 pm

    Hello, dear pros!
    I need to calculate the following:
    I am paid 25 cents for the first 150 miles. And if I drive more than 150 the difference is multiplied by 40 cents. What the formula would be?

    Thank you for your help in advance!

    Reply
    • Catalin Bombea

      April 25, 2020 at 3:44 am

      Ok, so for the first 150 miles your get 25 cents, if you have 190 miles, you get 25 cents for the first 150 and 40 cents for the rest of 40 miles?
      Try:
      =IF(A1<150,A1*25,150*25+(A1-150)*40)

      Reply
  40. Bharath

    April 22, 2020 at 1:28 pm

    Please help. I need your help to correct the below formula. .

    =IF(AND(G1>=DATE(2019,12,15),J1=”Listed”),”Yes”,”No”)&IF(AND(G2>=DATE(2019,12,31),J2=”Listed”),”Yes”,”No”)

    basically i want
    G1>=DATE(2019,12,15),J1=”Listed” to be “Yes”or G2>=DATE(2019,12,31),J2=”Listed” to be “Yes” if not “No”

    I just need answer to be one “Yes” or “No”. At the movement I am getting 2 answers like “YesYes” or YesNo” etc because of the & symbol I used in the formula and I am not sure what to use instead of & or if I can use a different formula.

    Reply
    • Philip Treacy

      April 22, 2020 at 1:38 pm

      Hi Bharath,

      I think this is what you need

      =IF(OR(AND(G1>=DATE(2019,12,15),J1="Listed"),AND(G2>=DATE(2019,12,31),J2="Listed")),"Yes","No")

      Regards

      Phil

      Reply
      • Bharath

        April 22, 2020 at 1:47 pm

        Thanks Philip

        I tried this formula earlier but it does not work.

        what i want is
        if a date is greater than or equal to 15 December 2019 and if it is Listed the Yes if not No
        i also need to add a formula in addition to the above
        if a date is greater than or equal to 15 March 2020 then Yes if not No

        Reply
        • Philip Treacy

          April 22, 2020 at 2:22 pm

          Hi Bharath,

          Without seeing your data and examples of the values in the cells, along with the expected results you want for those values, it’s too easy to misunderstand the the problem when explained merely in words.

          For example, what exactly do you mean by ‘I also need to add a formula’? How exactly? How does the 2nd formula interact with the first one?

          Please start a topic on our forum and attach your workbook with sample data and expected results from that data.

          Regards

          Phil

          Reply
  41. Siyabonga

    April 17, 2020 at 1:35 am

    Hi There,
    Quick question is it possible to have the (And,Or,IF) in one function and where logical tests are made up of different small referencing formulas.

    Reply
    • Mynda Treacy

      April 17, 2020 at 8:30 am

      Yes, it’s possible!

      Reply
  42. Corr

    April 14, 2020 at 3:14 am

    This if what I could come up with….=IF(Inverter!D$19=1;Inverter!$C3;IF(Inverter!D$19=2;Inverter!$D3;IF(Inverter!D$19=3;Inverter!$E3;IF(Inverter!D$19=4;Inverter!$F3;IF(Inverter!D$19=5;Inverter!$G3;IF(Inverter!D$19=6;Inverter!$H3;IF(Inverter!D$19=7;Inverter!$I3;IF(Inverter!D$19=8;Inverter!$J3;””)))))))), but the problem is that I need 15 IF statements and I am sure you can get the sequence. Is there a different formula to do the same. So I want to check cell D19 what ever the number in that cell return the same value in that numbers column.

    Reply
    • Catalin Bombea

      April 14, 2020 at 2:02 pm

      Hi Corr,
      Try:
      =INDEX(Inverter!$C3:$J3,MATCH(Inverter!D$19,Inverter!$C2:$J2,0))
      The formula will search the D19 value into Inverter!$C2:$J2 range and will return the values from Inverter!$C3:$J3 range, you will have to organize your data this way.

      Reply
  43. Tomáš Martínek

    April 12, 2020 at 8:11 pm

    hello, i want do a teble where i can see % of income foreach hour that is shop that i work in open. I would liek to do it like from 10:00 to 10:59 was sold stuf worth 75€ and from 11:00 to 11:59 25€ so from 10:00 to 10:59 my shop get 75% of income. i want to run in for like 1 month of days and i don´t want to do it manualy is there a way to make a function for it? if so can you help me how to make it? I think it should be someone think like— found everywhere IF date from 10:00:00 to 10:59:00 them get sum of 5 to left on x. … but how to write it in code … 😀

    Reply
    • Catalin Bombea

      April 13, 2020 at 1:04 pm

      Hi,
      If you have store data, you don’t need formulas, a simple pivot table report will do.
      Can you upload sample data on our forum? This way we will be able to give you a functional solution.
      Catalin

      Reply
  44. Smita

    April 8, 2020 at 1:22 am

    In excel using formula/function If any two months sale is less than 70% out of 3 months result should be “More work” otherwise blank. Even one month sale is less than 70% it should be also blank. Please help to solve.

    Reply
    • Mynda Treacy

      April 8, 2020 at 11:17 am

      Hi Smita,

      The formula will depend on the structure of your data. Please post your question and sample Excel file on our forum where we can help you further.

      Mynda

      Reply
  45. Freddie romero

    March 20, 2020 at 4:09 pm

    If average is greater than 100 then show the date today else add 3 on today’s date

    Reply
    • Mynda Treacy

      March 20, 2020 at 8:24 pm

      Hi Freddie,

      =IF(average of whatever you're averaging > 100, TODAY(), TODAY()+3)

      Mynda

      Reply
  46. Minhas Mehmood

    March 17, 2020 at 4:31 pm

    I need help please.
    if(a1>0,”1″,if(a2>0,”2″,if(A3>0,”3″,”0″)))

    I have 3 column and I need that if one column having value greater than 0 than its shows 1, if two column have values greater than 0 than shows 2 ….and so on…

    Can anyone help me please in this?

    Reply
    • Philip Treacy

      March 17, 2020 at 7:19 pm

      Hi Minhas,

      Use COUNTIF

      =COUNTIF(A1:A3,”>0″)

      Regards

      Phil

      Reply
    • Catalin Bombea

      March 17, 2020 at 7:19 pm

      A COUNTIF should give you the count: =COUNTIF(A1:A3,”>0″)

      Reply
  47. Saif

    March 16, 2020 at 5:33 pm

    if j5>0 then j5*g7,if j6>0 then j6*h7, if j5 & j6>0 then j5*g7+j6*h7
    L.E.: Please help formula for this equation

    Reply
    • Catalin Bombea

      March 17, 2020 at 3:00 am

      =IF(J5>0,J5*G7,0)+IF(J6>0,J6*H7,0)

      Reply
  48. Faisal Shahzad

    March 1, 2020 at 9:22 pm

    Hi
    can you help me to find a value
    if A1,B1,C1,D1,E1 have values like that
    0, 0, 2, 0, 0
    How we found this value
    C1 value not specified its Formula based value
    Regards

    Reply
    • Catalin Bombea

      March 2, 2020 at 2:58 am

      Hi Faisal,
      Only one of the sample cells can have a value greater than zero? Or there can be more than one?
      Please provide more details about what is needed, if more than one cells is greater than zero, which one should be returned, the first value, the last?

      Reply
  49. Nikil

    February 27, 2020 at 8:46 pm

    Please my formula

    IF(((G4-F4=0, “+” & TEXT(ABS(G4-F4),”hh:mm”)))

    Reply
    • Catalin Bombea

      February 27, 2020 at 10:15 pm


      =IF(G4-F4=0, "what should happen if result of logical test is zero?", "+" & TEXT(ABS(G4-F4),"hh:mm"))

      Reply
  50. Megan L Funneman

    February 22, 2020 at 2:44 am

    I’m trying to do a formula so that if B1= “s”, then P7. If B1=”m”, then Q7. If B1=”h”, then R7. I’m able to get a formula for just two of the possibilities, but when I add the third option, I’m retrieving an error.

    Reply
    • Mynda Treacy

      February 22, 2020 at 12:15 pm

      Hi Megan,

      Try:

      =IF(B1="S",P7, IF(B1="m",Q7, IF(B1="h",R7)))

      Mynda

      Reply
  51. LISA HAYS

    February 22, 2020 at 2:40 am

    In cell B8, enter an IFS function to return 0 earned if the Total Lessons in cell B6 is less than 5, return 1 earned if the Total Lessons is less than 10, otherwise return 2 earned.

    Hint: Type TRUE as your logical_test3 argument so that the function returns 2 earned if the other logical tests are false.

    Totally lost on this one problem involving multiple work sheets in workbook. Keep getting an error

    Reply
    • Mynda Treacy

      February 22, 2020 at 12:13 pm

      Hi Lisa,

      This sounds like an exam question, so I don’t want to solve it for you, but here is a tutorial on the IFS function, which they’re asking you to use. Hopefully that will point you in the right direction. If you’re still stuck, please post your question on our forum where we can help you further.

      Mynda

      Reply
  52. Gerhard Bester

    February 19, 2020 at 8:37 pm

    Good day.

    I have a start date and a estimated end date. (normally 3 years plus from actual start date). I have an additional cell that needs to have 1st, 2nd and 3rd which is supposed to update automatically linked to a today date.

    =IFS(TODAY()>=EDATE(L2;24);”3rd”;TODAY()=EDATE(L2;12);”2nd”;TRUE;”1st”)

    Actual Starting Date Estimated Completion Date (Trade Test) Progression (1st / 2nd / 3rd)
    2019/01/07 2021/01/07 1st

    The problem is that the formula does not calculate the 2nd.

    Actual Starting Date Estimated Completion Date (Trade Test) Progression (1st / 2nd / 3rd)
    2019/01/07 2021/01/07 1st should have been a 2nd year based on formula. Anniversary date2020/02/07 = 2nd and 2021/02/07 = 3rd

    Reply
    • Catalin Bombea

      February 20, 2020 at 3:25 am

      Hi Gerhard,
      The order of the arguments is important. Also, EDATE returns a date that is exactly 12 or 24 months after start date, so EDATE(DATEVALUE(“2019/01/07”),12) returns 2020/01/07, if you compare it with TODAY(), only in a very specific start day-today combination the logical test will be true, I guess you have to review the logical test as well.

      Reply
  53. CLAIRE BROWN

    February 14, 2020 at 3:23 am

    Hi I’ve done this before but cant remember the correct formula…

    I’m looking at getting an answer to a sum depending on if a number is 25 or 50. Ie if 50 then the sum be needs to be multiplied by 2.

    I am using this formula but i just get the answer saying FALSE when i actually need to the figure in numbers.

    =IF(D43=”25″,(G43/C43),IF(D43=”50″,((G43/C43)*2)))

    Reply
    • Mynda Treacy

      February 14, 2020 at 12:10 pm

      Hi Claire,

      When your criteria is numbers you don’t use double quotes. Double quotes are only for text. Try:

      =IF(D43=25,(G43/C43),IF(D43=50,((G43/C43)*2)))

      Mynda

      Reply
  54. Mandy

    February 13, 2020 at 12:52 am

    Hi, I am trying to do a formula but I think I am missing something or have it completely wrong. What I need to do is determine the data in cell A2 based on what I type in A1. e.g

    If if type “Blue” into cell A1 then I want cell A2 to say “Green” but if I type “Red” into cell A1 then I want cell A2 to say “Orange”, but if A1 is blank, I want A2 to be blank too.

    I have tried doing an IF and an IF/OR formula but it still doesn’t seem to work. Any help you could give would be gratefully appreciated.

    The formula I had is =IF(A1=Blue,Green,)IF=(A1=Red,Orange,),” “)))

    Thank you

    Mandy

    Reply
    • Philip Treacy

      February 13, 2020 at 10:27 am

      Hi Mandy,

      Try this

      =IF(A1="Blue","Green",IF(A1="Red","Orange",""))

      Regards

      Phil

      Reply
  55. Mabel

    February 11, 2020 at 6:48 am

    I have a checklist that must be checked with an “x” for Excellent, Satisfactory or Needs Improvement columns. Each Row has categories that need to be judged. The categories range from 2 to 6 per section. I want the overall judgment row to automatically show “x” when there are four or three let’s say excellent judgment in those rows with four. If there are six categories, if there are six, five or four x, the x automatically shows in the overall, and so on. I have been trying to use the If formula but cannot figure it out. Thank you.

    Reply
    • Mynda Treacy

      February 11, 2020 at 9:36 am

      Hi Mabel,

      We’d love to help, but it’s a bit difficult to picture your data. If you post your question on our Excel forum you can also upload a sample Excel file and we can help you further.

      Mynda

      Reply
  56. Jason Mack

    February 8, 2020 at 2:03 am

    I’m having an issue with the following formula.

    =if(OR(E2=”Jason”,”June”),D2,D2/2)

    I want to display the value in D2 if E2 is either ‘Jason’ or ‘June’ and if anything else(not ‘Jason’ or ‘June’) divide D2 by 2.

    Any help would be welcomed! Thanks!

    Here is the error produced:
    Error
    OR expects boolean values. But ‘June’ is a text and cannot be coerced to a boolean.

    Reply
    • Catalin Bombea

      February 8, 2020 at 3:02 am

      Hi Jason,
      You’re close:
      =if(OR(E2=”Jason”,E2=”June”),D2,D2/2)

      Reply
  57. Desmond

    February 7, 2020 at 1:40 am

    =IF(E17>1277777.78,23000,(1.8/100)*E17):IF(E17<138,888.89,2500)
    Can someone says what wrong with this statement

    Reply
    • Catalin Bombea

      February 7, 2020 at 3:15 am

      Before the second IF you have a colon: “:IF”
      What are you trying to do?
      “:” is a range operator, works with ranges, not values. A1:A10 will work, as it’s a valid range

      Reply
  58. Robin das

    February 6, 2020 at 11:30 am

    facing a simple problem. I want to put a formula if cell a6=p then I need 500 to be on a cell, and if cell a6=a then I need 0 or blank on the same cell.

    Reply
    • Mynda Treacy

      February 6, 2020 at 12:17 pm

      Hi Robin,

      Try:

      =IF(A6="p",500,IF(A6="a",""),"")

      Mynda

      Reply
  59. LeAnn

    February 4, 2020 at 10:10 am

    Good Evening,

    I have a graded vesting table and I need to have a formula for the following:

    Less than 2 years = 0
    2 years but less than 3 = 20%
    3 years but less than 4 = 40%
    4 years but less than 5 = 60%
    5 years but less than 6 = 80%
    6 years and thereafter = 100%

    I’ve tried some IF statements but cannot get it to work correctly. Any help would be appreciated.

    Thanks!

    Reply
    • Catalin Bombea

      February 4, 2020 at 4:46 pm

      Hi LeAnn,
      Try:
      =INDEX({0,0.2,0.4,0.6,0.8,1},MATCH(A1,{0,2,3,4,5,6},1))

      Reply
    • Mynda Treacy

      February 4, 2020 at 4:47 pm

      Hi LeAnn,

      You should use a sorted VLOOKUP formula for this, as explained here: VLOOKUP Sorted List Explained

      Mynda

      Reply
  60. Faisal

    February 2, 2020 at 6:00 am

    Hi,
    Please help me to correct my farmula for incentive sheet

    =IF(F5105%,H10, )

    The formula shown Result is #Value!

    Reply
    • Philip Treacy

      February 2, 2020 at 11:53 am

      Hi Faisal,

      I don’t know what you want to do with the formula so I’m guessing to give you an answer.

      It looks like you are testing the value if F5 to see if it is 105% of something.

      If this is true then you’ve written that the answer will be the value in H10. You then have a comma but haven’t specified an answer if F5 is not 105% of something.

      Really, everything you need to fix this is already in this article.

      But, if you have a value in A1 then you could write this

      =IF(F5=A1*1.05,H10)

      which omits a value if the test is false, in which case the answer will be the Boolean value False.

      You could also explicitly specify a value for the false result like this

      =IF(F5=A1*1.05,H10,A2)

      Regards

      Phil

      Reply
      • Faisal

        February 3, 2020 at 8:03 pm

        Thanks for Reply Phil

        Actually i am making a Incentive Calculation Sheet for our employees
        and i am stuck off and need your help

        Example

        if D2 is = to 100%, 101%,102%,103%,104%,105%
        then shown the unit that is calculating in slab 100%. 101%,102%,103%,104%,105%

        Regards

        Reply
        • Philip Treacy

          February 3, 2020 at 10:59 pm

          Sorry Faisal still not clear.

          Is D2 100% of what? What am I testing D2 against?

          Also not sure what you mean by ‘shown the unit that is calculating in slab …’

          Please start a forum topic and supply a workbook – that will make things easier to understand.

          Regards

          Phil

          Reply
          • Mynda Treacy

            February 4, 2020 at 5:36 pm

            Hi Faisal,

            Cell E6: =IF(C6>=100,100,0)
            Cell F6: =IF(C6>=105,5,0)
            Cell G6: =IF(C6>105,C6-105,0)

            In future, please post your questions on our Excel forum where you can upload your sample file and we can help you more promptly.

            Mynda

          • Philip Treacy

            February 4, 2020 at 10:46 pm

            Hi Faisal,

            Cell E6: =IF(C6>=100,100,0)
            Cell F6: =IF(C6>=105,5,0)
            Cell G6: =IF(C6>105,C6-105,0)

            In future, please post your questions on our Excel forum where you can upload your sample file and we can help you more promptly.

            Phil

  61. Amol Gharpure

    January 18, 2020 at 12:27 pm

    I want to set a excel formula as follows:
    I want assign 0 marks for persons scoring up to 74.99% and from 75 to 100 % marks scored, assign 20 marks on pro-rata basis.

    Kindly suggest the formula.

    Reply
    • Catalin Bombea

      January 18, 2020 at 4:04 pm

      Hi Amol,
      Try this:
      =INDEX({0,20},MATCH(A1,{0,75},1))

      Reply
  62. U T

    January 12, 2020 at 1:25 pm

    Kindly suggest formula to find pass or fail in different cell as c2, f2, h2, k2, n2, and q2 but need to pass in 4 subjects out of 6 and passing marks is 40.

    Reply
    • Catalin Bombea

      January 12, 2020 at 11:51 pm

      Hi,
      It’s best if you can upload a sample file with your data structure and details about what you are trying to achieve, hard to visualize what you have in those cells you mentioned.
      Simply create a new topic after signing up to our forum, you will be able to upload there.

      Reply
      • UT

        January 13, 2020 at 12:44 am

        I don’t understand you reply only in indirect way that pretend you cannot give answer

        Reply
        • Mynda Treacy

          January 13, 2020 at 8:52 am

          Without seeing your data it is too hard to write an answer that will be accurate. You will then reply asking for modifications so if we can see your data all of that can be avoided.

          So, if you can start a topic on the forum and attach your workbook that will help us help you.

          We also need a clear explanation of what the inputs are and what the expected outputs are. As it is what you have already stated is not clear enough.

          Regards

          Phil

          Reply
  63. Ali Rehman

    January 12, 2020 at 4:02 am

    Hi,
    I want to calculate the sum of the cells if in column B have text “paid” then calculate values from Column A in respective cell and if Column B have text “Un-Paid” then the values from Column A will not calculated… reference list is below…

    Column A Column B
    Rs 3,000.00 Paid
    Rs 30.00 Paid
    Rs 50.00 Paid
    Rs 5,000.00 Paid
    Rs 10,000.00 Un-Paid
    Rs 10,000.00 Un-Paid
    Rs 5,000.00 Un-Paid
    Rs 330.00 Un-Paid
    Rs 650.00 Paid
    Rs 785.00 Un-Paid

    Looking forward for prompt response please…

    Thanks

    Reply
    • Mynda Treacy

      January 12, 2020 at 6:09 am

      Hi Ali,

      You need a SUMIF formula for this.

      Mynda

      Reply
      • Ali Rehman

        January 12, 2020 at 6:19 pm

        Mynda kindly describe the SUMIF formula in details..

        i.e write the formula for my task please….

        Thanks

        Reply
        • Catalin Bombea

          January 12, 2020 at 11:49 pm

          Hi Ali,
          You have here an article about SUMIF.

          Reply
  64. Joseph P K

    January 3, 2020 at 3:18 pm

    i need a help if i put above 1 in i4, it will be reflected in i5 the value of i4*8-8, that means if i4 is 1.25 the value of i5 is 2 (1.25*8-8)

    how i given if formula

    pl. help

    Reply
    • Catalin Bombea

      January 3, 2020 at 4:05 pm

      Hi Joseph,
      In cell I5 you should have this formula:
      =I4*8-8

      Reply
  65. BHUSHAN CHHAGAN LANJEWAR

    January 2, 2020 at 4:49 pm

    I need help to put formula in excel. If 1-50 then rate is 9.85, if 51-100 then rate is 9.55, if above 100 then rate is 9.10. Please calculate the formula for excel sheet.

    Reply
    • Mynda Treacy

      January 2, 2020 at 9:47 pm

      Hi Bhushan,

      =IF(A1>100,9.1,IF(A1>=51,9.55,IF(A1>=1,9.85)))

      Where the value you’re checking is in cell A1.

      Mynda

      Reply
    • Catalin Bombea

      January 3, 2020 at 2:14 am

      Try a simple index match version:
      =INDEX({0,9.85,9.55,9.1},MATCH(A1,{0,1,50,100},1))

      Reply
  66. Vix

    December 12, 2019 at 11:35 pm

    A. B. C. D. E.
    1. 20. 30. 40 50. 60.
    2. 59. 62. 79. 80. 81
    3. 78. 98. 108. 118. 128

    If someone has a salary let’s say 30(B1) and our promotion process is 2 steps front {50}and the move one grade down to closest number 59(a2).
    L.E.:
    Sorry the question was what formula would help.

    Reply
    • Catalin Bombea

      December 13, 2019 at 1:45 am

      Hi Vix,
      Please upload on our forum a sample file with a few manual examples, it will be easier to understand the situation and provide a functional solution.
      Catalin

      Reply
  67. Andy

    December 8, 2019 at 3:16 am

    B2*C2=E2 then E2+B2=F2 then F2=B3

    18,185.78 X 12.8% = 2,327.78
    18,185.78 + 2,327.78 = 20,923.83

    20,923.83……..
    all im trying to do is have the excel automatically track the financial interest, where all I have to do is change the interest of the given year.
    I’m still green behind the ears.
    Thank you
    Andy

    Reply
    • Mynda Treacy

      December 8, 2019 at 8:55 am

      Hi Andy,

      Thanks for your question. Can you please post it on our Excel forum where you can upload a sample Excel file to explain your requirements as I’m not following the explanation here, sorry.

      Mynda

      Reply
  68. deena

    November 30, 2019 at 11:10 pm

    i need help with this question {Write a formula for Gross Pay column for each employee as 40*(Rate per Hour) +(Hours Worked-40)*1.5*Rate per Hour if Hours Worked is larger than 40 and Rate per Hour*Hours Worked if Hours Worked is less than 40} and thank you.

    Reply
    • Catalin Bombea

      December 3, 2019 at 12:02 am

      Try:
      =IF(A1-40>=0, Rate*(40+(A1-40)*1.5), Rate*A1)

      Reply
  69. Sherry Roark

    November 25, 2019 at 10:03 am

    I am trying to figure out the formula for : IF function with a nested AND function to test for lunch profits (in cell B2) greater than or equal to $100 (Cell E2), that returns the work BEST when both conditions are met. I have gotten this far but then I am lost .. IF(AND(B2=”lunch”,E2=>=100), “BEST” but this is not working please help !

    Thank you so much for your assistance. !

    Reply
    • Catalin Bombea

      November 25, 2019 at 1:41 pm

      Hi Sherry,
      You can try:

      =IF(AND(B2="lunch",E2>=100), "BEST","not best")

      Reply
  70. garry edmunds

    November 23, 2019 at 10:20 am

    Found the problem and it works great! Thanks Mynda you are the best!!

    I realized I need a new step in the formula and wonder if it may be possible to do.

    with the same formula and if A1 was cat could I add the number in E1, and if A1 was dog could I subtract the number in E1?

    Reply
    • Mynda Treacy

      November 23, 2019 at 11:18 am

      Yes, just modify the formula in the ‘value if true’ argument.

      Reply
      • Garry

        November 23, 2019 at 12:29 pm

        thanks for the quick reply

        so would the formula would be =if(A1=”cat”,C1-D1+E1,if(A1=”dog”,D1-C1-E1,0))

        or would it be different?

        Reply
        • Mynda Treacy

          November 23, 2019 at 1:27 pm

          Probably. If it doesn’t give you the result you want, then modify the formula. You can always create the formula in another cell and when it’s working as desired, copy that formula into the value_if_true argument of the IF formula.

          Reply
  71. Garry

    November 21, 2019 at 2:10 pm

    I have been racking my brain on how to write a formula for this.

    in cell F:1 if A:1 = “cat” then sum C:1- D:1, or if A:1 = “dog” then sum D:1-C:1

    This is probably a simple formula but I am having a hard time figuring it out.

    Thanks

    Reply
    • Mynda Treacy

      November 21, 2019 at 3:52 pm

      Hi Garry,

      =IF(A1="Cat",C1-D1,IF(A1="Dog",D1-C1),0)

      Mynda

      Reply
      • Garry

        November 22, 2019 at 1:00 am

        Thanks for responding, but when I use that formula I get an error that says I have entered too many arguments for this function and doesn’t give me the results I need.

        Reply
        • Garry

          November 22, 2019 at 1:09 am

          also I don’t want a value of 0 returned if false, i wanted the sum of if first statement is met to be c1-d1, or if second statement is met the sum d1-c1, i don’t need the word “false” to show up in the cell

          Reply
          • Mynda Treacy

            November 22, 2019 at 9:25 am

            Sorry, Garry. Parenthesis in the wrong place. Try this:

            =IF(A1=”Cat”,C1-D1,IF(A1=”Dog”,D1-C1,0))

            Replace the zero with whatever you want to show up in the case that cell A1 doesn’t contain Cat or Dog.

            Mynda

          • Garry

            November 22, 2019 at 10:21 am

            Thanks for the correction, it got rid of the error but it still isn’t giving me what I need. f1 just gives me a 0 no matter what is in A1.

            I am probably not explaining it clearly so I will try to explain it one more time. In the cell f1 I would like a calculated numerical value if A1 has the word cat in the cell I would like the sum for c1-d1 but if A1 has the word dog in the cell I would like the sum for d1-c1.

            Example: A1 will either have the word cat or dog in it and nothing else, so if c1 has the number 50 and d1 has the number 45 I would need: if cat then subtract 50 – 45 = 5 or if dog subtract 45 – 50 = -5

            The formula as written, regardless if it has cat or dog in A1 always comes back 0 which isn’t correct.

            I hope this makes sense. Thanks

          • Mynda Treacy

            November 22, 2019 at 10:33 am

            I tested the formula using the criteria you provided and it works for me. I wonder if cell A1 doesn’t just contain the word ‘Dog’ or ‘Cat’ but perhaps this word is one of many text characters in the cell. Have you checked to make sure there isn’t a space after Dog or Cat e.g. ‘Dog ‘ These spaces are not visible on the face of the cell. You need to edit it and see where the cursor stops. If that’s not it, please post your question on our Excel forum where you can upload a sample Excel file and we can troubleshoot further.

            Mynda

  72. Mudita Nangia

    November 18, 2019 at 6:33 pm

    Hi,

    I want to excel for the training program, where I have around 90 employees who need to complete 4 training. this training need to be refreshed every two year. so for every year I want the percentage complete and not complete data, this data should also include the employee who need the refresher course and whether they completed or not.

    Can you help me with the formula.

    Reply
    • Catalin Bombea

      November 18, 2019 at 8:05 pm

      Can you please upload a sample file on our forum?

      Reply
      • Mudita Nangi

        November 18, 2019 at 10:06 pm

        How can I upload the file. I tried but cannot see any option for uploading.

        Reply
        • Mynda Treacy

          November 19, 2019 at 7:53 am

          Hi Mudita,

          On the forum, when you create a new topic there is a button under the section where you type your question for ‘Attachments’.

          Mynda

          Reply
          • Mudita Nangia

            November 19, 2019 at 2:55 pm

            I have uploaded the sample report under the post “Training Record – Query”, can somebody please reply. It is urgent.
            L.E.:
            Forum: General Excel Questions & Answers
            Topic: Training Record – Query
            Avatar Mudita Nangia – 17 hours ago

            Here is where I posted the query with sample report

  73. Connie Jensen

    November 16, 2019 at 1:58 am

    If Sheet Jan 18 cell A1 is >135 result should be FT if Sheet Jan 18 A1 is <136 result should be PTE if Sheet Jan 18 A1 is 0 result should be PT
    Is this formula possible?

    Thank You

    Reply
    • Mynda Treacy

      November 16, 2019 at 9:42 am

      Hi Connie,

      On sheet Jan 18 enter this formula:

      =IF(A1>135,"FT",IF(A1<136,"PTE",IF(A1=0,"PT")))

      Mynda

      Reply
  74. Hazeez-ur-Rehman

    November 15, 2019 at 5:00 pm

    I am making result I wanted to do if a student fails in 1 subject then excel shows supply if he fails in 2 subjects then excel shows 2 supply but if he fail in 3 or more than 3 subjects then excel shows it Fails Whats the formula I can apply?

    Reply
    • Catalin Bombea

      November 15, 2019 at 6:01 pm

      How is your data organized? You just have the number of passed exams in a column, or each exam has a separate column?
      Please use our forum to upload a sample file, it will be easier to help you.
      Catalin

      Reply
  75. Jalen

    November 15, 2019 at 8:31 am

    Hello, Im working with alot of data trying to find a formula that will help me with it. Myt example is that if columns B and C match H and I then use the information in J and K to fil in columns D and E.

    Any assistance would be appreciated. Thank you

    Reply
    • Philip Treacy

      November 15, 2019 at 8:59 am

      Hi Jalen,

      You’ll need to provide more specifics for us to give you a good answer. What type of data is stored in Cols B and C? Are we checking for a date, a string, a number?

      How exactly do you want to use the info in Cols J and K?

      If you open a topic on the forum and supply your workbook with data it’ll be easier to give you some help.

      Regards

      Phil

      Reply
  76. hailey

    November 7, 2019 at 9:03 am

    How do I enter an IF,OR function to find out if D2 is greater than or less than 2 or G2 is “yes” or “no”

    Reply
    • Philip Treacy

      November 7, 2019 at 10:24 am

      Hi Hailey,

      Did you give t a try yourself?

      Here’s the formula

      =IF(OR(D2<>2,G2=”yes”,G2=”no”),TRUE,FALSE)

      So if D2 is not equal to 2 or G2 contains either yes or no, you’ll get the result TRUE, otherwise the result is FALSE.

      Regards

      Phil

      Reply
  77. Sol Wize

    November 4, 2019 at 3:15 am

    Hi,

    I’m trying to fill a cell value based on whether another cell has any value whatsoever. So if value of Cell A2 is ABC, Cell E2 should have value 1.

    I use the value in E2 to sum up the total.

    Now the problem I face is that although I’m getting the Value 1 in E2 using IF function. The data is not considered numeric & the sum is always zero.

    But if I manually enter 1 in each cell, it is ok. Kindly help.

    I’m using the below formula:

    =IF(([@BARCODE]>1),”1″)

    Here barcode is the name of the field.

    Reply
    • Catalin Bombea

      November 4, 2019 at 3:49 am

      Hi Sol,
      Whatever you put between double quotes will be text.
      Try:
      =IF(([@BARCODE]>1),1)

      Reply
  78. Fazal

    November 3, 2019 at 8:22 pm

    Need help urgently please

    I am using – formula like
    =H8-i8-f8

    Now i want to write in the answer that if the answer of above formula is in “-” then type 0. And if answer in positive then write the answer….

    Reply
    • Catalin Bombea

      November 4, 2019 at 12:42 am

      Try:
      =IF(H8-i8-f8<0,0,H8-i8-f8)

      Reply
  79. WASEEM.NM

    November 2, 2019 at 8:13 am

    =IF(E3=”above”,F3=D3,””))

    I am using this formula to evaluate true or false as the answer. But not sure by what logic it is showing wrong as false /true in some of the cells and some of the cells the output is correct.

    Regards,
    Waseem.Nm

    Reply
    • Mynda Treacy

      November 2, 2019 at 1:21 pm

      Hi Waseem,

      In English your formula says:

      If the contents of cell E3 is “above” then F3 equals D3 otherwise blank.

      In other words, if E3 contains “above” then Excel tests to see if F3 equals D3, if it does then it returns TRUE, if it doesn’t then it returns FALSE. I suspect you want something else in this argument.

      Mynda

      Reply
  80. Chris

    November 1, 2019 at 9:51 pm

    VBA for following function:
    If a1 > b1 and c1 > d1 or a1 < b1 and c1 < d1 then lock cells e1 and f1 else unlock cells e1 and f1

    Reply
    • Catalin Bombea

      November 1, 2019 at 11:25 pm

      You almost finished writing it:
      If ([a1] > [b1] and [c1] > [d1]) or ([a1] < [b1] and [c1] < [d1]) then [e1:f1].Locked=True else [e1:f1].Locked=False End If

      Reply
  81. Mzamo

    October 17, 2019 at 1:23 am

    Hi, I need help, I want to delete G3 if W3 is blank, how do I do this using which function?

    Reply
    • Mynda Treacy

      October 17, 2019 at 10:13 am

      Hi Mzamo,

      Formulas can’t delete values from cells. Only VBA can do that. The best you can do is have the formula return a blank e.g.

      =IF(W3=””,””,the result you want)

      Mynda

      Reply
  82. candice

    October 8, 2019 at 2:34 am

    I have a workbook with Sheet 1 and Sheet 2. In Sheet 1 colume B, each cell (going down the colume) has a number in it from 1.0-9.23. Each number has a matching statement of meaning. The numbers and their matching meaning are listed in Sheet 2 in columes A and B (A has the number, B has the definitions).
    In sheet 1 colume B, when every a number is entered from 1.0-9.23, I want it to pull the matching defintion in the cell from Sheet 2 colume B. Can I do that?

    Reply
    • Catalin Bombea

      October 8, 2019 at 6:59 am

      Sure, you can try a simple INDEX-MATCH combination in cell C2 of Sheet1:
      =INDEX(Sheet2!B:B,MATCH(B2,Sheet2!A:A,0))

      Reply
  83. kay

    October 7, 2019 at 7:26 pm

    hello can anyone help me. my formula doesnt work
    =IF(H4=380000,”10000″,IF(H4>380000,”20000″,IF(H4>570000,”30000″,IF(H4>850000,”40000″,IF(H4>1050000,”60000″,IF(H4>1300000,”80000″,IF(H4>1800000,”100000″,IF(H4>2000000,”120000″))))))))

    Reply
    • Mynda Treacy

      October 7, 2019 at 8:31 pm

      Hi Kay,

      It looks like you’re testing numbers, but returning text because all of your ‘value_if_true’ arguments are surrounded in double quotes. If you want numbers returned then you don’t need double quotes. Double quotes are only for text.

      That said, you should use this VLOOKUP on a sorted list technique for formulas like that.

      Mynda

      Reply
  84. Ared

    October 4, 2019 at 3:15 am

    I am in need of a nested formula that would allow for the following in one cell:

    If A2=”A”, and B2=”I”, then C2=”Low”, or, If A2=”A”, and B2=”II”, then C2=”Low”, or, If A2=”A”, and B2=”III”, then C2=”Low”, or, If A2=”A”, and B2=”IV”, then C2=”Moderate”, or, If A2=”A”, and B2=”V”, then C2=”High”

    My issue is there are 25 data possibilities. How do I write that in a formula?

    I II III IV V
    A Low Low Low Moderate High
    B Low Low Moderate High Unacceptable
    C Low Moderate Moderate High Unacceptable
    D Low Moderate High High Unacceptable
    E Moderate Moderate High Unacceptable Unacceptable

    Reply
    • Catalin Bombea

      October 4, 2019 at 5:27 am

      You can try an INDEX MATCH formula:
      Assuming that you have that table f values in a sheet named Sheet2, with I,II,III,IV, V in cells B1:F1, and A,B,C,D,E in cells A2:A6, try:
      =INDEX(Sheet2!A1:F6,MATCH(A2,Sheet2!A1:A6,0),MATCH(B2,Sheet2!A1:F1,0))

      Reply
  85. Theo

    October 3, 2019 at 9:17 pm

    Please help. how can I get Cell A1 to auto update with values from the information in the cells below the dates, on a daily basis?

    Eg. Cell A1 = -1,26% (currently ties up with 02-Oct)

    01-Oct / 02-Oct / 03-Oct / 04-Oct
    6,56% /-1,26% /-1,63% / 0,00%

    Cell A1 should now have -1.63% (should tie up with 03-Oct) and update daily thereafter with the value for that day.

    Reply
    • Catalin Bombea

      October 4, 2019 at 5:36 am

      in A1 you should have:
      =INDEX(A3:AA3,MATCH(TODAY(),A2:AA2,0))
      In row 2 you should have dates, not dates in text format.

      Reply
  86. Jax

    October 3, 2019 at 4:21 pm

    Hi
    i need to auto fill data on column B when i write column A

    like IF A:A =”done” then B:B =”done”

    so if i write done on column A i need it to update automatically on Column B

    please Help out

    Reply
    • Mynda Treacy

      October 3, 2019 at 5:07 pm

      Hi,

      In B1 you have =A1 and just copy that down Col B

      Phil

      Reply
  87. Siddu Goudar

    September 26, 2019 at 4:59 pm

    80% -90%
    91%-99%
    100%-110%
    111%-120%

    >120%
    how i can solve it

    Reply
    • Catalin Bombea

      September 26, 2019 at 8:14 pm

      =INDEX({1,2,3,4,5,6},MATCH(A1,{0, 0.8, 0.9, 1, 1.1, 1.2 },1))

      Reply
  88. Kalyani Jayram Kharde

    September 20, 2019 at 6:22 pm

    There are 7 subjects out of 25 but having a rule if 1 subject having less than(<=7) then show result F1, for two subject having less than 7 then F2 otherwise Pass if greater than 7.

    I can did it with countif function. It give me a count but i want count like 1 for F1, 2 For F2…..7 for Fail.

    Please Help me…

    Its Urgent

    Reply
    • Mynda Treacy

      September 20, 2019 at 8:09 pm

      Hi Kalyani,

      Please post your question on our Excel forum with a sample file because our answer will depend on your data and file set up.

      Mynda

      Reply
  89. Tara

    September 17, 2019 at 12:00 pm

    NEED URGENT HELP
    I need a column to spit out “Yes” when the term “Optum” or “United” is anywhere in the cell in another column.
    So if any of these two terms are there, it should say ‘Yes’ in a new column.

    Reply
    • Catalin Bombea

      September 17, 2019 at 1:51 pm

      Try:
      =IF(OR(ISNUMBER(SEARCH(“optum”,A1)),ISNUMBER(SEARCH(“united”,A1))),”Yes”,”No”)

      Reply
  90. Md. Hasnain

    August 27, 2019 at 2:46 am

    Basic Salary 600000 taka and House rent 60% of basic but I do not give him more than 20000 taka in House rent in this case what is the formula I should follow when I make a salary sheet.

    Reply
    • Philip Treacy

      August 27, 2019 at 8:50 am

      Sorry, I don’t understand what it is you ar trying to do. Can you please try explaining it again in a different, clearer way.

      Phil

      Reply
  91. JD

    August 24, 2019 at 4:19 am

    Hi. Can you please help me. I already have the IF formula where i want to generate a specific value from a column if between date range.
    Example:
    Column A1 Start Date, Column B1 End Date, Column C1 Headcount
    Data in 2nd row: Start date Jan. 1, 2014 up to Jan.31, 2015, Headcount 6
    FOrmula in Column D1
    =IF(AND(D1>A2, D1<B2), C2, 0)
    The formula is working fine but the problem is when there is blank or no date in End date column, there is no data generated. I would like to make use of one formula only that will work whether the End date has data or not.

    Your help is very much appreciated. Thanks

    Reply

    Reply
    • Catalin Bombea

      August 25, 2019 at 7:57 pm

      Hi,
      It’s not clear what means for you “no data is generated” when there is no End Date. Your formula returns a zero in this case. What should be the expected result if End Date is missing?

      Reply
  92. Oggi

    August 7, 2019 at 7:37 pm

    Hello,

    has anyone have solution for this task.

    I have kilometer range and the prices in onother column.
    0-15 = 4,67
    16-30 = 5,86
    31-45 = 6,61
    and so on.
    I would like to input price for any range via some function.
    For example if I input 19 km in another column gets te value 5,86.

    Thank you guys for yor help.

    Have a nice day

    Reply
    • Mynda Treacy

      August 8, 2019 at 11:56 am

      Hi Oggi,

      You can use a VLOOKUP on a sorted list as explained here.

      Mynda

      Reply
  93. v

    July 23, 2019 at 10:42 pm

    Total Days Income Expenses Remained Days remaining per day exp Days can spend
    31 2000 200 1800 20 500
    i want formula for how many days i can spend by per day 500 in remaining cash

    Reply
    • Philip Treacy

      July 24, 2019 at 8:40 am

      Hi,

      This isn’t clear to me.

      If you have 200 income and 200 expenses then your remaining cash is 1800.

      Where did you get 500 from?

      Is 20 the number of days you want to split the 500 over? If so you just need 500/20?

      You can get a whole number of days by using

      =ROUNDDOWN(500/20,0)

      Regards

      Phil

      Reply
  94. jay

    July 19, 2019 at 10:26 am

    please help me regarding to my formula,
    i just want to identify the days of pending, if cell C2 = 20/07/2019 05:21 then cell E2 my formula =IF(ISBLANK(C2),””,TODAY()-$C$2) “i want if c2 is blank so nothing will showed and if c2 have date and time will calculate the days of pending old date – date today,, example answer like this “2 days”

    Reply
    • Catalin Bombea

      July 19, 2019 at 4:56 pm

      Like this one?
      =IF(ISBLANK(C2),””,Round(TODAY()-$C$2,1) & ” days”)

      Reply
      • jay

        July 22, 2019 at 10:03 am

        i already try this one but not working the days not counting if how many days of pending. it showed only 0days, even i try to adjust the date.

        Reply
        • jay

          July 22, 2019 at 10:18 am

          anyone can help for this one,,
          C2(date and time) = 20/07/2019 05:21
          D2(status, Pending or Resolved) = Resolved
          E2(days of pending)=IF(ISBLANK(C2),””,IF(COUNTA(C2),$U$1-(C2),IF(D2=S2,E2=”Resolved”,””)))

          i want to count the days of pending date today – C2 and if the c2 is blank e2 also blank, and if the d2 was pending still counting the days of pending but if the d2 is Resolved E2 will showed Resolved please help to correct my formula thank you and advance.

          Reply
          • Catalin Bombea

            July 22, 2019 at 4:51 pm

            Please use our forum, it’s much easier and faster to get help, you will be able to upload sample files so we can see your data structure.

        • Catalin Bombea

          July 22, 2019 at 4:50 pm

          Hi Jay,
          Please upload sample file here, on our forum, no idea what you’re actually doing there.

          Reply
  95. Val

    July 15, 2019 at 9:55 am

    “If they are full time, their bonus is 10% multiplied by the average of everyone’s salary, otherwise the bonus is 5% multiplied by the average of everyone’s salary”

    I’m not sure about how to perform this on my excel worksheet.
    Are they asking for the average of each person or all the employees’ salary?

    Reply
    • Mynda Treacy

      July 15, 2019 at 12:01 pm

      Hi Val,

      I’m assuming there is only one salary listed per person, so you can’t find the average of a single salary therefore it must be the average of ‘everyone’s salary’, as stated in the question.

      Mynda

      Reply
  96. eshwar

    July 14, 2019 at 5:00 am

    i want calculate the discount offer ,,, if the order value is 1000 the percentage of discount is 20% up to amount of 200 in excel

    Reply
    • Catalin Bombea

      July 14, 2019 at 3:30 pm

      Hi Eshwar,
      What if the order value is less or higher than 1000? Can you provide some examples?

      Reply
  97. Karen Gustafson

    July 4, 2019 at 1:27 am

    Formula is =IF(($B$9+$B$13-$B$20)<0,0,round(($B$9+$B$13-$B$20),2)). Please define "<0,0,round" and ",2".

    Reply
    • Catalin Bombea

      July 5, 2019 at 1:13 am

      Hi Karen,
      Here is a translation:
      “IF the sum of those 3 cells is less then zero, return a zero, otherwise round the sum of those 3 cells to 2 decimal places.”

      Reply
  98. Georgiana

    July 3, 2019 at 6:20 pm

    Please help me with the function for this conditions, in one:

    3 + 1 >240
    2 + 1 >360
    1 + 1 >480

    Thanks!

    Reply
    • Catalin Bombea

      July 5, 2019 at 1:19 am

      Hi Georgiana,
      What 3+1, 2+1, 1+1 represents? They can never be over 240, or 360 or 480.

      Reply
  99. Hope

    June 16, 2019 at 8:54 pm

    How do i calculate individual rating score as per rating scale of a staff using staff id as reference?

    I need to find all 1 score ratings until 5 as the highest score rating

    For example:

    Rating Score Score
    Staff Name: 1 2 3 4 5

    Anna 5 3 7 8 9 – Total number of Ratings

    Reply
    • Mynda Treacy

      June 17, 2019 at 8:46 am

      Hi Hope,

      It’s difficult to picture the layout of your data to provide you with a solution. Please post your question on our Excel forum where you can upload a sample Excel file and we can help you further.

      Mynda

      Reply
  100. Hari Lamichhane

    June 5, 2019 at 2:34 am

    I would like to use if function to pull data from certain cell, for example formula in cell B9, if value in H9 is greater 0, put data from B9 to B9, if value in H9 is greater than 0 than put data from C9 to C9, and so on and if value in H9 is greater than 0, then put value from H9 to H9.

    If H9 is blank, go to the next cell and put value from next cell to B10, C10, D10. Below is sample data.

    Date:B9 Chk# C9 Memo#D9 Payee#E9

    01/05/2019 19757 May 2019 Rent City of Toronto

    Account#F9 Amount#H9

    22-01-00 – Accounts Payable $31,313.55

    Data to be tested contained in column H. Column G contains other data.

    Thanks in advance
    Hari

    Reply
    • Mynda Treacy

      June 5, 2019 at 12:01 pm

      Hi Hari,

      Thanks for your question. It’s a bit tricky to follow. Are you able to post it on our Excel Forum where you can upload a sample Excel file so we can see your question in context and help you further?

      Thanks,

      Mynda

      Reply
  101. Nageeb

    May 26, 2019 at 6:07 pm

    I want Excel to do following in cell P3:

    1- if N3 is Not Blank then Now()-N3, and if result of Now()-N3 equal to or grater than 3 years then it return “DUE” but if result less than 3 Years it return “VALID”
    2- if N3 is Blank and L3 is Not Blank then Now()-L3, and if result of Now()-L3 equal to or grater than 3 years then it return “DUE” but if result less than 3 Years it return “VALID”
    3- if N3 is Blank and L3 is Blank then Now()-J3 and if result of Now()-J3 equal to or grater than 3 years then it return “DUE” but if result less than 3 Years it return “VALID”

    Looking for your support

    Reply
    • Catalin Bombea

      May 29, 2019 at 1:24 pm

      Hi Nageeb,
      Try this:
      =IF(MAX(N3,L3,J3)=0,””,IF(NOW()-MAX(N3,L3,J3)>=365.25*3,”DUE”,”VALID”))

      Reply
  102. Appiah Yaw Kusi

    May 22, 2019 at 7:57 am

    Hello, i want to get total aggregate of my students score in exams who offers 8 subjects. 4 of the subjects are compulsory to add up their grades after which i select two best out of the remaining four and add them up get my total aggregate. How do i go by that.

    Example if i have 1, 3, 4, 1, 2, 5, 4 and 5 as the grade. The first four is compulsory to sum up their grades after which i select two grades out of the remaining four and sum all 6 grades up as my total aggregate. Pls what excel formular should i use.

    Reply
    • Mynda Treacy

      May 22, 2019 at 9:06 am

      Hi Appiah,

      Thanks for your question. Can you please post it on or Excel forum where you can upload a sample Excel file so we can better understand what you’re trying to do and help you with a solution.

      Thanks,

      Mynda

      Reply
  103. Amin Suchwani

    May 17, 2019 at 2:45 pm

    income exceeds 1200000 but does not exceed Rs. 2,500,000 the rate of income tax is 5% of the amount exceeding Rs. 1,200,000 or Rs. 2000 which one is greater

    Reply
    • Mynda Treacy

      May 18, 2019 at 1:57 pm

      Hi Amin,

      =IF(AND(A1>1200000, A1<2500000),MAX((2500000-A1)*5%,2000),0)

      Where A1 contains the Income value.

      Mynda

      Reply
  104. Hillary

    May 16, 2019 at 12:23 am

    I am having trouble with my if/and formula. I looking at ranges 0-.89=1, .90-1.09=2, 1.10+=3. My formula is =IF(AND(A2>=0,A2=0.9,A2=1.1,3)))
    I am getting FALSE on .89, .90, 1.09 and 1.10 but everything else is assigned the correct 1,2 or 3. Example: .88 is coming back with 1 but not .89 is coming back as false and should be 1.

    Any thoughts?

    Thanks!

    Reply
    • Hillary

      May 16, 2019 at 12:25 am

      =IF(AND(A2>=0,A2=0.9,A2=1.1,3)))

      Not sure why my formula above didn’t copy correctly

      Reply
      • Philip Treacy

        May 16, 2019 at 4:35 pm

        Hi Hillary,

        Copy from where?

        Without knowing what your desired result is I can’t be sure how to fix the function, but this is valid syntax, although logically it will always be False as you have two tests for A2 to be equal to a value.

        =IF(AND(A2>=0,A2=0.9,A2=1.1),3)

        Regards

        Phil

        Reply
    • Philip Treacy

      May 16, 2019 at 4:49 pm

      Hi Hillary,

      Try this

      =IF(AND(A2>0,A2<=0.89),1,IF(A2<=0.9,2,3))

      Please note that if A2 is empty, 0 or negative, this will return a result of 2. This shouldn’t be the default answer but the AND condition makes it that way.

      Either make sure A2 has a value inside your ranges, or include another IF to prevent erroneous output:

      =IF(OR(A2<0,A2=0,A2=""),"",IF(AND(A2>0,A2<=0.89),1,IF(A2<=0.9,2,3)))

      This will return an empty string “” if A2 is negative, 0 or empty.

      Phil

      Reply
  105. Sanchez

    May 15, 2019 at 1:05 pm

    Hi Sanchez again,

    Thank you but the COUNT will just work for one department. I have multiple departments so would essentially need a formula to look at department and whether FTE or PT then grab number of positions and sum them.
    Department FTE/PT # of Positions Available
    HR FTE 1
    HR FTE 1
    HR FTE 1
    HR PT 1
    HR PT 1
    IT FTE 2
    IT PT 1
    IT PT 1
    Finance FTE 20
    Finance FTE 3
    Finance PT 2
    Finance PT 1
    Customer Support FTE 10
    Customer Support PT 1
    Customer SUpport PF 1

    Reply
    • Catalin Bombea

      May 15, 2019 at 8:53 pm

      Try:
      =SUMPRODUCT((A$1:A$10=A1)*(B$1:B$10=B1)*(C$1:C$10))

      Reply
  106. Nora

    May 15, 2019 at 7:47 am

    IF CELL A1 =Jennifer then value will be 2000 please help me generate a formular for this

    Reply
    • Catalin Bombea

      May 15, 2019 at 12:15 pm

      Hi Nora,
      =IF(A1=”Jennifer”,2000,0)

      Reply
  107. Sanchez

    May 14, 2019 at 1:38 pm

    Department FTE/PT # of Positions Available
    HR FTE 1
    HR FTE 1
    HR FTE 1
    HR PT 1
    HR PT 1
    Jan
    FTE
    PT
    Can you please help me with a formula to group and sum FTE and PT? Please use example above. The formula should be something like IF department is “FTE” = 3 and IF department is “PT”=2. Thank you!

    Reply
    • Catalin Bombea

      May 14, 2019 at 1:51 pm

      Hi Sanchez,
      The easiest way is to add a pivot table: select the data range, from ribbon-Insert-Pivot Table-New Worksheet, then from the list of headers add the department to the row section, and the same department to the values section.
      Or use: =COUNTIF(A1:A10,”FTE”)

      Reply
  108. Marc

    May 13, 2019 at 7:23 pm

    Hello, i am running some assessments with various criteria to be met by candidates who are scored. Is there a way i can show their total score against a pre-populated “maximum possible”? So:

    A B C D E Total
    1. John 3 3 2 3 2 13/20 (or 13 out of 20)
    2. Sue /20
    3. Paul Out of 20
    4. Chris
    5. Abby

    So the words “out of ‘total'” (or number equivalent) are already in col. F, but column F would also have a formula that calculates the candidates score and then puts the 2 together. I know we could simply write the words out of 20 in the column F header, but there are reasons (long, boring ones) why it has to be done this way

    Thanks in advance

    Marc

    Reply
    • Catalin Bombea

      May 14, 2019 at 1:54 pm

      Just use:
      =SUM(B2:F2)&”/20″
      A percentage will be relevant too, 100% means 20/20:
      =SUM(B2:F2)/20 (format the cell as percentage)

      Reply
  109. Crispin

    May 6, 2019 at 11:11 am

    You guys are good

    Reply
    • Mynda Treacy

      May 6, 2019 at 11:16 am

      🙂 thanks, Crispin! Glad we can help.

      Reply
  110. Robert

    May 3, 2019 at 8:00 am

    HI!
    I am making a calculator which multiplies a value by a percentage. in some cases, we need to enter the word “Special” instead of the calculation.
    Obviously I am getting the #Value! error.

    I am not sure if this is correct:

    =IF(OR(D4*I8), “SPECIAL”)

    so I want it to enter the total of the multipliaction from D4*I18 in cell D13, or if the cell has the word “Special” in it, I want it to show Special in D13 instead.

    Reply
    • Mynda Treacy

      May 3, 2019 at 8:52 am

      Hi Robert,

      Assuming D13 is the cell that you want to test to see if it contains the word ‘Special’:

      =IF(D13="Special", "Special', D4*I8)

      In English the formula reads:

      IF D13 contains the word ‘special’, then return ‘special’, otherwise C4*I8.

      Mynda

      Reply
  111. Sabrina

    April 19, 2019 at 10:44 am

    I need help with this formula, I have a multi worksheet workbook, in the summary worksheet I need the cell to just return a yes if there is text or a number in the other worksheet cell and a no if there if the cell is a blank on the other worksheet. I tired this. =IF(ISTEXT(vlookup(A7,’csa’!$A:$AC,14,False),”YES”, IF(ISNUMBER(vlookup(A7,’csa’!$A:$AC,14,FALSE)), “YES”, IF(ISBLANK(vlookup(A7,’csa’!$A:$AC,14,FALSE), “NO”, “”))) but it is too many arguments

    Reply
    • Catalin Bombea

      April 19, 2019 at 2:39 pm

      Hi Sabrina,
      You are missing some paranthesis(after FALSE, there should be 2 closing paranthesis):

      =IF(ISTEXT(vlookup(A7,'csa'!$A:$AC,14,False) ) ,"YES", IF(ISNUMBER(vlookup(A7,'csa'!$A:$AC,14,FALSE)), "YES", IF(ISBLANK(vlookup(A7,'csa'!$A:$AC,14,FALSE) ) , "NO", "")))

      Reply
  112. Sha

    April 15, 2019 at 3:59 am

    How do I write a formula IF cell A1= <4 then value will be a defined number…say 69?

    Reply
    • Mynda Treacy

      April 15, 2019 at 9:23 am

      Hi Sha,

      =IF(A1=<4, 69,0)

      The formula above reads; if cell A1 is less than or equal to 4 then return 69, otherwise return 0.

      Mynda

      Reply
      • sha

        April 15, 2019 at 10:50 am

        Perfect.
        What if there are more than one criteria? If cell A1 is less than 4 then return 69 or if cell A1 is between 5-9 then the return is 79?

        Thank you

        Reply
        • Mynda Treacy

          April 15, 2019 at 1:07 pm

          Then you should use VLOOKUP on a sorted list like this.

          Reply
      • Sha

        April 15, 2019 at 7:16 pm

        Formula that will automatically calculate total price for any amount of units placed in C12.

        Unit Quantity Price per unit
        Below 4 36
        5 to 10 34
        11 to 30 31
        31 to 5 26
        51 above 20

        # of Units C12
        Total Price: ____

        Reply
        • Mynda Treacy

          April 15, 2019 at 9:59 pm

          Hi Sha,

          You should use a VLOOKUP formula for this, as explained here: VLOOKUP approximate match.

          Mynda

          Reply
  113. Abubakar Isiaq

    April 8, 2019 at 7:39 pm

    Hi, what would be the formular to access the first 1000 from 20000, second 1000 from the 20000 and so on

    Reply
    • Catalin Bombea

      April 8, 2019 at 8:55 pm

      Hi,
      Try this one:
      OFFSET($K$1:$K$1000000, (ROW(A1)-1)*1000,0,1000)
      In the first cell, this formula will return a range with first 1000 cells. If you copy this down, the second cell will have the second 1000 items, and so on. Use SUM, COUNT or your desired function with this range.

      Reply
  114. Shae

    March 29, 2019 at 12:21 pm

    I am wanting to group employees into 3 categories (good, better,best) based off of their yearly averages but I was wanting that to auto populate. So if there yearly average false into a range (10-15%) they are good is there a formula for that. It’s seems as thought the if and works but would be very complicated seeing that it’s 3 categores

    Reply
    • Philip Treacy

      March 29, 2019 at 3:33 pm

      Hi Shae,

      Yes this can be done. You haven’t specified the ranges for each of the 3 categories so it’s hard to give you a solution without all the information.

      Can you please open a topic on our forum and supply a workbook with the data needed to provide you an answer.

      Regards

      Phil

      Reply
  115. Harry Staniforth

    March 26, 2019 at 5:27 am

    In a measured audit the auditor can give a 1,2,3,4 or 5 score. I want to transfer these to 0,25%,50%,75% and 100% respectively in the final sheet. Is there a formulae for this?

    Reply
    • Mynda Treacy

      March 26, 2019 at 12:51 pm

      Hi Harry,

      If you have Office 365 you can use the SWITCH function. Otherwise, you’d have to write an IF function for each score.

      Mynda

      Reply
    • Catalin Bombea

      March 26, 2019 at 2:06 pm

      Hi Harry,
      You can try this:
      =INDEX({0,0.25,0.5,0.75,1},Score)
      Replace Score with a cell reference where you have that value.

      Reply
  116. Elizabeth Reading

    March 14, 2019 at 11:55 pm

    I would like to highlight a row of cells if 3 or more cells in the row meet the criteria – is there a way to do this please? Alternatively, if a rule could be created to put a word in a cell if three or cells in a row meet the criteria then I could use auto formatting to highlight those rows. The difficulty I am having is trying to identify those with 3 or more (out of a maximum of 9) meet the criteria. Any help would be much appreciated.

    Reply
    • Mynda Treacy

      March 15, 2019 at 9:27 am

      Hi Elizabeth,

      You can use conditional formatting to highlight the row, as explained here. If you get stuck please post your question and sample Excel file on our forum where we can give you a specific answer.

      Mynda

      Reply
  117. Rachel Vickers

    March 1, 2019 at 11:36 pm

    Hiya. I have a list of cost types. If the cost type is Staff Costs, I need to be able to take the value of that staff cost (appears in another cell), find 15 percent of it, and detail what that is in another cell. I’ve got to that point, but it’s taking the value of costs from rows detailing other cost types, which means when I total it in ANOTHER column, it’s adding double.

    I don’t know if that makes sense!

    But this is what i have so far:

    =IF(C4=”Staff costs (direct and external)”,M4 *0.15,M4)

    But when I then sum this, as I said, the cells that dont have staff costs as the type are still pulling through, so any total is including this. How do I negate anything BUT staff costs (direct and external) in this formula.

    ANY HELP much appreciated! Thank you

    Reply
    • Philip Treacy

      March 2, 2019 at 1:42 pm

      Hi Rachel,

      Can you please open a post on the forum and supply you workbook, it’ll make it easier to understand your problem and offer a solution.

      Regards

      Phil

      Reply
  118. Elly

    February 21, 2019 at 11:38 pm

    Hi Phil,

    I’d need your advice on the following:

    I currently have 2 columns of value – Column A and Column B. However, there are some “0” value and or “*KEY_ERR” in Column A.

    I’m hoping for Column C to pick values in Column A as priority. However, if Column A has “0” or “*KEY_ERR” value, I’d then want Column C to pick the values in Column B. I was able to get the formula to work in Column C if Column A value is “0” but I’m not able to get the formula to work to reflect Column B’s value if Column A value is “*KEY_ERR” at the same time.

    Could you advise? Thanks in advance!

    Reply
    • Philip Treacy

      February 23, 2019 at 3:55 pm

      Hi Elly,

      In Col C you want this

      =IF(OR(A1=0,A1=”KEY_ERR”),B1,A1)

      Regards

      Phil

      Reply
  119. Lisa Nelson

    February 21, 2019 at 8:16 am

    I tried to post a comment, but not sure if it went through.

    I am trying to use an if then statement to get a date in column A to show up in COlumn B as just the month (but i am using it in a pivot table so it has to not be a date that is just formatted to show up as a month).

    so i need if A2 contains or starts with January or 1, then return January in B2. eg. A2 is 1/2/19 and i want B2 to say Jan but as text.

    dont know if you can help or not!

    thanks

    Reply
    • Philip Treacy

      February 23, 2019 at 3:59 pm

      Hi Lisa,

      In B2

      =TEXT(A2,”mmm”)

      Regards

      Phil

      Reply
  120. Frances

    February 21, 2019 at 5:32 am

    I need the formula that would look at a cell and compare to another cell….

    If cell i7 is equal to J7 then it is true if not it is false.
    Thank you

    Reply
    • Philip Treacy

      February 21, 2019 at 3:04 pm

      Hi Frances,

      There are multiple examples of this on the page, have you tried to work this out for yourself? The best way to learn is by doing.

      =IF(I7=J7,TRUE,FALSE)

      Phil

      Reply
  121. Racel

    February 20, 2019 at 8:42 pm

    Hi there,

    I need help with a simple formula in excel.

    I’m creating a table for Debts.

    So the actual sheet are consists of cells namely –Amount I owe– then several cells for –Payment– then a cell for the –Remaining Balance–.

    The scenario is, if I owe 2000 then I wasn’t able to pay full but I only pay it for like 1000 then next time 500 then lastly 500.

    So what could be the right formula for this matter.

    Can you please help me if how the formula should be?

    Hoping for your kind answers.

    Thank you

    Racel

    Reply
    • Philip Treacy

      February 21, 2019 at 3:10 pm

      Hi Racel,

      Its always better to provide your data in a workbook.

      This can easily be done but I need your workbook, it’s too complicated to explain clearly in writing.

      Please open a topic on the forum and attach your workbook.

      Regards

      Phil

      Reply
  122. Nicole Villangca

    February 19, 2019 at 8:03 pm

    Im doing a computation of tax. Here’s the scenario, for the Tax Exempt, the limit is 90,000.00.
    What formula will I use if the total tax exempt not exceed 90,000 then there should be an excess amount, but if the total tax exempt exceeds 90,000 it should be zero.

    Please correct this formula
    =IF(D3>1,”Copy the number in D3″, “0”

    Reply
    • Mynda Treacy

      February 19, 2019 at 8:04 pm

      Hi Nicole,

      =IF(D3>1,D3,0)

      Mynda

      Reply
  123. Jason

    February 19, 2019 at 2:25 am

    I am really new to excel and not too sure what formula to use.

    Basically what I am trying to do is, if the value in B5 = 5 units, then for every 5 units that must equal to 15 points.

    E.g. For every 5 units sold, that will then equal to 15 points

    Not sure if this makes sense

    Reply
    • Philip Treacy

      February 19, 2019 at 12:14 pm

      Hi Jason,

      I’m not entirely clear. Is what you are trying to say that for every 5 units sold, you get 15 points? So 10 units = 30 points etc?

      If so you can use this =FLOOR(A1,5)/5*15

      You can read up on FLOOR and CEILING

      Regards

      Phil

      Reply
  124. Steve Loveland

    February 18, 2019 at 8:40 pm

    Hi all,

    I am looking for a variation of what is proposed here. I am looking to autopopulate a list that goes something like this…
    IF Cell A1 = x AND Cell B1 = y THEN C1 = z
    where x is a service; y is a supplier and z is a shortcode.

    While the formula is quite simple, I have 190 conditions! I am not sure if there is a better approach to this. I’m not sure if reading an array is the right approach.

    Ideally, I will ‘teach’ the worksheet the codes for each service and supplier and run a macro or piece of VBA code over the list for the first two columns.

    Is this the most useful approach? Should I be using VBA for this?

    Reply
    • Philip Treacy

      February 19, 2019 at 12:03 pm

      Hi Steve,

      Impossible to say without seeing your data.

      Can you please open a topic on the forum and supply your workbook.

      Regards

      Phil

      Reply
  125. JOE

    February 16, 2019 at 2:43 am

    CAN YOU USE AN IF STATMENT AND ALLOW FOR USER INPUT IN THE SAME CELL? I WANT TO USE AN IF STATMENT TO REFERENCE ANOTHER CELL; IF IT IS TRUE IT WILL ALLOW THE USER TO ENTER A NUMBER IN THE ACTIVE CELL AND IF FALSE IT WILL RETURN A STRING.
    E.G. =IF(B5=”FIG #2″, USER INPUT, “N/A”) THIS WOULD BE TYPED INTO CELL C5 FOR EXAMPLE

    Reply
    • Philip Treacy

      February 16, 2019 at 9:20 pm

      Hi Joe,

      No you can’t do this with IF, but you could use VBA.

      Regards

      Phil

      Reply
  126. Fabio MUscedere

    February 15, 2019 at 7:45 am

    I would like a formula for excel if cell G32 = 6.00 to 13.00 = ATHLETIC or 14.00 to 17.00 = FIT or 18.00 to 24.00 = AVERAGE or 25.00 Over = OBESE

    Reply
    • Philip Treacy

      February 15, 2019 at 11:54 am

      Hi Fabio,

      You’ve specified ranges of values where you have a specific result, but not specified what happens if the value is in between those ranges.

      For example, what is the result if the value in G32 is 13.5? Or 17.5, 18.5 etc?

      You can use this formula and adjust as needed

      =IF(AND(G32>=6,G32<=13),"ATHLETIC",IF(AND(G32>=14,G32<=17),"FIT",IF(AND(G32>=18,G32<=24),"AVERAGE",IF(G32>=25,”OBESE”,””))))

      Regards

      Phil

      Reply
  127. Anwar Ali

    February 14, 2019 at 11:28 pm

    I have a question for calculating incentive
    If one sales man sales more than 75% of his target than 1% of sales amount and if sales was more than 100% than 2% or if sales was less than 75% than incentive will be “0”
    Example
    Sales Amount Target Achievement Incentive Amount
    1130 1500 75% 1% ? (Need formula)
    1510 1500 100% 2% ? (Need formula)
    1000 1500 66% 0% ? (Need formula)

    All need in one formula

    Reply
    • Philip Treacy

      February 15, 2019 at 12:22 pm

      Hi Anwar,

      If Sales are in ColA and Target is in ColB the in ColC:

      =IF(A1/B1>1,A1*0.02,IF(A1/B1>0.75,A1*0.01,0))

      Regards

      Phil

      Reply
  128. Michael Loewen

    February 14, 2019 at 10:57 am

    I have two sheets in an excel workbook. I need Sheet 2 to show the whole row 3 of Sheet 1 if the value in column 1 of Sheet 1 is 56

    Reply
    • Philip Treacy

      February 14, 2019 at 11:37 am

      Hi Michael,

      Do you really need to copy the entire row or can you limit it to certain cells? It would be better to limit it.

      You also don’t say which cell in Col1 is 56, so I am working with A1

      Try this on Sheet2 in A1 which will copy A3:Z3 from Sheet1. =IF(Sheet1!A1=56,Sheet1!A3:Z3,””) and you will need to enter this as an array formula by using CTRL+SHIFT+ENTER

      https://www.myonlinetraininghub.com/excel-multi-cell-array-formulas

      Regards

      Phil

      Reply
  129. Ron Schmidt

    February 14, 2019 at 6:10 am

    Sheet 1 has rows of data on individuals. One cell value in the row determines if the entire row is copied from sheet 1 to sheet 2. Example: if K2 = 1 in sheet 1, all of row 2 (A through M) needs to transfer to sheet 2 automatically.

    Reply
    • Philip Treacy

      February 14, 2019 at 11:25 am

      Hi Ron,

      On Sheet2 in A1 you can enter =IF(Sheet1!K2=1,Sheet1!A2:M2,””) but you will need to enter this as an array formula by using CTRL+SHIFT+ENTER

      https://www.myonlinetraininghub.com/excel-multi-cell-array-formulas

      Regards

      Phil

      Reply
  130. Abdul muneer

    February 11, 2019 at 2:59 pm

    i need formula if A coloum(0 to 9)b colum(9 to 1)c coloum (0 to 9) ,d coloum a1(0 )then answer a1,a2,a3,a4,a5.same e coloum b2 then answer b3:b8 ,

    Reply
    • Philip Treacy

      February 11, 2019 at 3:11 pm

      Hi Abdul,

      It’s not clear what you want or how your data is structured.

      Please open a post on the forum and supply your data in a workbook.

      Regards

      Phil

      Reply
  131. Rena

    February 9, 2019 at 6:39 am

    Hello,

    I am in need of a formula to find duplicates in 2 columns.
    For e.g.
    cell A1: 123 & cell B1: 1
    cell A2: 345 & cell B2: 0
    cell A3: 123 & cell B3: 1
    cell A4: 123 & cell B4: 2

    I need to identify that A1,B1 & A3,B3 are a duplicate.
    Meanwhile A2,B2 & A4,B4 are not duplicate.

    Reply
    • Catalin Bombea

      February 9, 2019 at 1:30 pm

      Hi Rena,
      Add a helper column with those 2 columns joined: =A1&B1
      Then, in another column, just count how many times the current row values appear in the helper column:
      =Countif(C1:C10,A1&B1)

      Reply
  132. Benjamin

    February 9, 2019 at 5:41 am

    Thank you so much for explaining all this. But i’m still a bit lost with this. I’m trying to add in a lunch break time in the formula below – if the total hours are over 6.

    IF(OR(ISBLANK(C2),ISBLANK(D2)),DURATION(0),D2−C2)

    Employee Date In Out Break Hours
    Benji 12/18/18 12:00 PM 6:00 PM :30 6

    Reply
    • Catalin Bombea

      February 9, 2019 at 1:42 pm

      Hi Benjamin,
      You are mixing time and decimal values. Keep in mind that the numeric value of 1 day is.. 1, that means 6 hours cannot be used as is, the unit of measure should be consistent. Therefore, 6 hours will be correctly represented by =6/24, because hours are just fractions of a day (6 hours=6/24=1/4=0.25= 1 quarter of a day).
      On the other side, whenever you see a time format in a cell, 12:00 PM for example, keep in mind that it’s just a formatted value, the number in that cell cannot be over 1 unit, in this case 12:00 PM will be 0.5 decimal units. Just change the format of that cell to Number, and you will see the real value excel is using in calculations.
      In your example, 6:00 PM – 12:00 PM will be 0.75-0.5=0.25. A lunch break of 30 minutes will be expressed as: 0.5/24, this is the value you need to add to your calculations.

      Reply
  133. Dejen

    February 5, 2019 at 6:49 pm

    Hi sir, it is a question;
    What is mean =IF(I4=N6,”-“,+H4/I4*31) in English.

    Thanks,
    Dejen

    Reply
    • Mynda Treacy

      February 5, 2019 at 8:58 pm

      Hi Dejen,

      If the value in I4 equals the value in N6, then place a – in the cell, otherwise divide H4 by I4 x 31.

      Mynda

      Reply
  134. Yugal

    February 2, 2019 at 9:05 pm

    If value is between 27601-27649 then round down to 27600, if > 27650 then value should be 27700

    the numbers may be any

    Reply
    • Mynda Treacy

      February 3, 2019 at 12:32 pm

      Hi Yugal,

      =IF(AND(A1>=27601,A1<=27649),27600,IF(A1>27650,27700))

      Where your value is in cell A1.

      Mynda

      Reply
  135. Desley Bee

    January 30, 2019 at 1:27 pm

    How do I calculate the ‘age’ (B3) from dob (B2) AND if dob (B2) is Blank than to leave the ‘age’ column blank

    Reply
    • Catalin Bombea

      February 1, 2019 at 7:25 am

      Hi Desley,
      Here is an article about date calculations that should help.
      If B2 is blank, just use a conditional formula in B3:
      =IF(Len(B20=0,””,put here the age calculation formula)

      Reply
  136. Deb

    January 30, 2019 at 2:16 am

    I am trying to get my formula to return a certain dollar amount based on the number entered and where it falls related to the goal. There are 3 levels, therefor 3 tier payouts possible. The first part of my formula is working and returning the correct dollar amount but the part that has 100 in the formula is not working.

    =IF(E5<B5,"$0",IF(E5=C5,75,IF(E5=D5,100)))))

    Reply
    • Mynda Treacy

      January 30, 2019 at 10:33 am

      Hi Deb,

      The formula looks ok except it has two too many closing parentheses.

      I suspect that one of the first two conditions is being met and so the third condition E5=C5 isn’t getting an opportunity to evaluate. It’s difficult to say much more without seeing your file. You can post your question on our Excel forum where you can also upload your Excel file so we can help you further.

      Mynda

      Reply
  137. DanielleK

    January 23, 2019 at 11:23 pm

    I am trying to find a formula and am looking for some help. I have a spreadsheet of different grants that were put into a home. For some of the overall project dollars, the acquisition price of the home is counted and for others it is not. For example

    House 1= grant $25000, owner contribution $25,000, acquisition $10,000, we are NOT counting acquisition.
    House 2= grant $25,000, owner contribution $30,000, acquisition $40,000, we ARE counting acquisition.

    Right now I have a Yes or No under column G for whether we are counting or not. How do I get a formula to automatically count the E column (acquisition cost), if G is Yes and not count it if G is No.

    Reply
    • Philip Treacy

      January 24, 2019 at 11:05 am

      Hi Danielle,

      When you say you want to ‘count’ the value in the E column, it’s not clear what you are doing with that value. But I can still write something that you can start with.

      If your formula is in H1 then you could use something like

      =IF(G1=”Yes”,E1+YourCalc,YourCalc)

      Where I have written YourCalc is where you need to insert your calculation for whatever you are trying to calculate. Please note I have just added the value in E1 to YourCalc if G1 is “Yes”. You’ll need to incorporate E1 into your calculation in the way that is correct for you.

      Regards

      Phil

      Reply
  138. Zahir

    January 23, 2019 at 2:12 am

    I have data in two different columns like…
    Description Amount
    Tanveer Rs 460.00
    Faisal Rs 50.00
    Ahmad Rs 480.00
    Tanveer Rs 550.00
    Tanveer Rs 50.00
    ———————————
    Grand Total Rs 1590

    Now i want subtotal without adding these values where in the same row text is “Tanveer”.
    Is there any formula which i use condition when in one column text is “Tanveer” then on this row in other column’s value will not add in total sum of all values without using filter or specify each cell number in formula…

    I will be very thankful if someone help out on this situation.

    Reply
    • Catalin Bombea

      January 23, 2019 at 3:00 pm

      Hi Zahir,
      Looks like you want to recreate what a pivot table does, with formulas. You should try a pivot table.
      If you want formulas only, please upload a sample file with a sample manual result on our forum.

      Reply
  139. Grant Penney

    January 22, 2019 at 2:16 am

    Hi can you help with this please?

    I have a formula in Cell A1 that shows a % to goal. I need a formula for Cell A2 that will show a number based on the % to goal showing in Cell A1.

    >=102% = 5
    >= 110%=10
    >=125%=15
    >=150%=30

    thanks in advance,
    Grant

    Reply
    • Catalin Bombea

      January 22, 2019 at 2:53 pm

      Hi Grant,
      try this:
      =INDEX({0,5,10,15,30},MATCH(A1,{0,1.02, 1.1, 1.25, 1.5},1))

      Reply
  140. Emilyn

    January 21, 2019 at 12:33 pm

    I am struggling with a nested IF question. Trying to calculate which employees get how much percentage of a bonus at year end.
    if E2 is greater than 9, they get 2%
    if E2 is greater than 7,and less than or equal to 9, they get 1.5%
    if E2 is between 5 to 7, they get 0.75%
    is E2 is less than 5, they get 0.25%

    Reply
    • Catalin Bombea

      January 21, 2019 at 4:17 pm

      Hi Emilyn,
      Try this one:
      =INDEX({0.25, 0.75, 1.5, 2},MATCH(A1,{0,5,7,9},1))

      Reply
  141. Kristy

    January 19, 2019 at 2:59 pm

    I am struggle on getting a formula to work correctly. Working on a rebate calculator.
    If total>=3500=$100 rebate, if total >=7000=$250
    >=15000=$750
    >=25000=$1500
    >=35000=$3000
    What I write: =IF(A20>=3500,100,IF(A20>=7000,250,
    Etc
    I only get the lowest level calculated. What am I doing wrong?
    Thank you.

    Reply
    • Catalin Bombea

      January 19, 2019 at 3:51 pm

      hi Kristy,
      try this one:
      =INDEX({0,100,250,750,1500,3000},MATCH(A20,{0,3500,7000,15000,25000,35000},1))

      Reply
      • Kristy

        January 20, 2019 at 4:16 am

        Thank you. Trying now. Help much appreciated!!

        Reply
  142. Phil

    January 17, 2019 at 2:20 am

    Hi Catalin
    I am not very good with Excel and formulas and I presume what I want to do can be done, I am just not sure how and what the formula might be.
    In the first line I have headers There are 20 in total, but only 11 of these are colour coded. I was able to search how to add a “check box” and put these into the 10 spaces to show a different stage of progress, and when one of these boxes are checked there is the 11 cell that I would like to show as the “Status, so for each line in the status column and be the same colour as the checked tab. If I were to write the code it might look like this (remember I suck at code writing)
    =D(COUNTSIF($E:$N)BOX MARKED IS CHECKED), COLOUR FILL WOULD = D

    I am hoping to capture multiple lines, based on a line by line series

    Reply
    • Catalin Bombea

      January 18, 2019 at 6:17 pm

      Hi Phil,
      Please upload a sample file with some data, and an example of the desired result. It’s not easy to visualize the data structure based on a description, a file will be more helpful than 1000 words.
      Use our forum to upload (create a new topic.)

      Reply
  143. Phil

    January 17, 2019 at 1:05 am

    I need help to try and figure out a formula for a template I am working on. I am going to insert 10 “check Boxes” each with a colour code, when any of these boxes is checked, I want another cell in the line to change to the same colour as the checked box. It is meant for production and to signify each stage of that production so at a quick glance I can see what the “status” is and for argument sake let’s say column D is the status cell, and from E to N are the progress steps each with its own colour code. When one of these 10 columns is checked, I want the D cell to change to that colour, for every row item (and there could be hundreds), in any line there should be only two colours highlighted, The D and one of the E-N both with the same colour code. Does this make sense? It would be easier to show a chart I think

    Reply
    • Catalin Bombea

      January 17, 2019 at 1:37 am

      hi Phil,
      Each check box can be linked to a cell at your choice, right click the check box and select Format Control, in Control tab select a linked cell. In your formulas, you can refer to the linked cell to display colours as needed.

      Reply
  144. Dewald Serfontein

    January 16, 2019 at 6:28 pm

    How do I do a calculation example. my answer is 49 then the IF statement says less than 50 and if my answer is 51 it says greater than 50 but now i also want a statement to say if it is between lets say 50 and 70 i want it to say between 50 and 70.

    Reply
    • Catalin Bombea

      January 18, 2019 at 6:14 pm

      Hi,
      Try this formula:
      =INDEX({“Less than 50″,”between 50 and 70″,”greater than 70”},MATCH(A1,{0,50,70},1))
      In cell A1 (change as needed), you should have those values: 49, or 51 that must be evaluated by the formula.

      Reply
  145. Shazia Classen

    January 15, 2019 at 4:30 am

    need the cell to show “1” if B4 equals 7 and “0” if B4 is greater than or less than 7.

    Reply
    • Catalin Bombea

      January 15, 2019 at 2:33 pm

      try this:
      =IF(B4=7,1,0)

      Reply
  146. Slim

    December 27, 2018 at 3:59 am

    Before entering the data under Product Unit Price, use a policy to accept price between $2 and
    $20. Input text Title is “Input Range” with message=”Price between $2 and $20”, and Error Alert
    having Title= “Wrong Input” and Error Message=”Invalid Value Entered”.

    what formula do I use and how to do it ??

    thank you

    Reply
    • Mynda Treacy

      December 27, 2018 at 9:38 am

      Hi Slim,

      You need Data Validation for this. If you’re still stuck after reading that tutorial, please post a question on our Excel Forum where you can also upload a sample Excel file.

      Mynda

      Reply
  147. bETH

    December 21, 2018 at 7:09 am

    If Cell A is >= 9 AND Cell B is => 9 then Cell C = YES and both Cell A and B must have a number 9 or more. i am trying and it is allowing a blank cell B to still but a Yes in cell C

    =IF(AND(a>=9,b>=9), “Yes”,” “)

    Reply
    • Philip Treacy

      December 21, 2018 at 10:15 am

      Hi Beth,

      You need to specify specific cells, not just ‘a’ or ‘b’

      =IF(AND(A1>=9,B1>=9),”Yes”,””)

      Regards

      Phil

      Reply
  148. Wisk Derifond

    December 21, 2018 at 3:16 am

    Hi,

    I am trying to calculate P=P1XP2 (Column K4) but I am finding errors.

    P1=Improbable (Column G4), P2=Unlikely (Column J4)

    I want P= Improbable

    Thanks,

    Reply
    • Philip Treacy

      December 21, 2018 at 10:13 am

      Sorry Wisk without seeing your data it isn’t clear how to calculate what you want. If you are trying to calculate P1 * P2 then isn’t it just P1 * P2?

      Please open a post on the forum and attach your workbook.

      Regards

      Phil

      Reply
  149. Lea

    December 20, 2018 at 11:47 pm

    Hi, may i asked you what is the meaning of this formula?

    =IF(F3-5>0,(F3-5)*30+5*15,F3*15)

    Reply
    • Philip Treacy

      December 21, 2018 at 10:10 am

      Hi Lea,

      If you break it down into its parts it’s quite straightforward.

      The syntax for IF is

      =IF( TEST, Value if True, Value if False)

      TEST = F3-5 > 0 in other words is F3-5 > 0 ?

      Value if TEST is True = (F3-5) * 30 + 5 * 15

      Value if TEST is False = F3 * 15

      So if we have the number 10 in cell F3 and the formula evaluates to

      =IF (10-5>0 , (10-5)*30+5*15, 10*15)

      well 10-5 = 5 which is greater than 0 so this test result is TRUE which means the IF formula gives you the answer

      (10-5)*30+5*15 = 225

      Regards

      Phil

      Reply
  150. SUMESH R KAMATH

    December 20, 2018 at 7:33 pm

    Hi,
    i need a formula for following calculation,
    if X =< 21,000, then value should be 1.75*X , else the value should be 0(zero) [ ie, when X is greater than 21,000 then value should be 0(zero) ]

    Reply
    • Mynda Treacy

      December 20, 2018 at 10:17 pm

      Hi Sumesh,

      Assuming X is in cell A1:

      =IF(A1<=21000, A1*1.75, 0)

      mYNDA

      Reply
  151. Masum

    December 20, 2018 at 3:17 am

    Dear,
    I would like to request you, i have more than amount from one column,may be 1 to more than 1500,
    i want to formula in excel under 50 of amount 10 %,up 100 of amount of 5 %, how to formula in excel,
    please help me.
    Lots of Thanks.
    Masum.

    Reply
    • Philip Treacy

      December 20, 2018 at 10:03 am

      Hi Masum,

      It’s not really clear to me what you need. Can you please supply a workbook with real data so it’s easier to help you. You can create a forum post and attach your workbook to that.

      Regards

      Phil

      Reply
      • Masum

        December 21, 2018 at 12:54 am

        Dear.
        First for sorry.I can not explain to you. for Example 20,25,33,40,42,45,48,50 under fifty 10%
        and 60,62,65……..100.105…..150 more of 5%
        10=10%
        20=10%
        30=10%
        35=10%
        50=10%
        51=5%
        55=5%
        58=5%
        60=5%
        70=5%
        100=5%
        150=5% etc.
        condition of under 50 all sales of customer commission
        for sales man 10% amount ,and up to 51 ,55,60,70,78,95,100,110 more,more
        of 5% .

        Reply
        • Philip Treacy

          December 21, 2018 at 10:38 am

          Hi Masum,

          So what I think you are trying to do is calculate commission. 10% for values < 50 and 5% for values greater than fifty. Assuming values can't be less than 0, and the sales amount is in A1 =IF(A1<50,A1*0.1,A1*0.05) Regards Phil

          Reply
  152. Sanjay Gundlavkar

    December 19, 2018 at 2:38 pm

    Can Anyone rectify my below formula.
    Instead of Result, it shows me as #VALUE!

    =IF(K3=0,”Nil”,TODAY()-C3)*AND(IF(K3>0,”Excess”,TODAY()-C3))*AND(IF(K3<0,TODAY()-C3,"Bal"))

    Reply
    • Philip Treacy

      December 19, 2018 at 3:04 pm

      Hi Sanjay,

      With 0 in all the specified cells, you are multiplying a string (from the IF) with a Boolean result (from the AND) and you get #VALUE!

      The use of IF inside AND isn’t correct either.

      If you specify what you are trying to do and the expected result it’ll be easier to help you.

      It looks like you are trying to say

      IF K3 = 0 then the result is “Nil”
      IF K3 > 0 then the result is “Excess”
      IF K3 < 0 then the result is "Bal" I'm not sure where the TODAY() fits in. When asking questions like this please ALWAYS provide the source data otherwise we are guessing. You can start a post on the forum and attach your workbook to that. Regards Phil

      Reply
  153. Mohsin

    December 14, 2018 at 12:45 am

    Urgent Need Help in MS EXCEL FORMULA

    Apply formula to fine the following result in above column “Result”:
    If the student fail in Theory and pass in practical then result display “Fail in Theory”.
    If the student fail in Practical and pass in Theory then result display “Fail in Practical”.
    If the student fails in Both, Theory and practical then result display “Fail in B
    Both”.

    Reply
    • Mynda Treacy

      December 14, 2018 at 9:27 am

      Hi Moshin,

      Please post your question on our Excel forum where you can upload a sample file. We need to know how Excel knows if the student fails or passes. How is your data laid out etc.

      Mynda

      Reply
  154. Hasan

    December 10, 2018 at 2:09 pm

    Hi,

    I need If A1 (date) past 3 days then H1 cell color would be Blue, and If the A1(date) past 5 days then H1 color would be red. How can I do this ?
    Can anyone give me shade light on this ?

    Reply
    • Mynda Treacy

      December 10, 2018 at 7:22 pm

      Hi Hasan,

      It’s not clear how you’re calculating when a date is past 3 days/5 days. Is it from today’s date, or a date in another cell.

      Either way, this post on how to apply conditional formatting using formulas should help.

      Mynda

      Reply
      • Hasan

        December 11, 2018 at 10:20 pm

        Hi Mynda ,

        Thanks very much for your effort and replied me so fast…..

        Reply
  155. Lokule

    December 9, 2018 at 11:10 pm

    It was more helpful to me

    Reply
  156. Sujit Pereira

    December 6, 2018 at 10:35 pm

    If column A contains month and column B contains names of clients. If the Name of a client has arrived before in same month earlier value in column C should be 0 and if it is first time value incolumn should be 1

    Reply
    • Catalin Bombea

      December 7, 2018 at 1:15 am

      Hi Sujit,
      Add a new column with this formula:
      =A2&B2 (a simple concatenation of first 2 columns)Drag the formula down to your range.
      In cell D2, add this formula:
      =IF(COUNTIF(C$2:C2,A2&B2)=1,1,0)
      Drag this formula down, it should return the expected results.
      Catalin

      Reply
  157. hemant

    December 5, 2018 at 10:45 pm

    how do i write formula in excel if cell A2 =Y then drop down list , if A2 =N then that cell should be non editable

    Reply
    • Catalin Bombea

      December 6, 2018 at 12:33 am

      Hi,
      A formula will never lock a cell, so you need to reformulate your question. Only with vba you will be able to set things your way.
      Regards,
      Catalin

      Reply
  158. Svea Mason

    December 5, 2018 at 7:19 am

    I am trying to copy a row of data from “PP Part 1” Tab (worksheet) to “SharePoint” Tab (worksheet) if AJ# <= 2. I want the formula to skip the copy if AJ# is not <= 2 and then evaluate the next row to see if it should copy without leaving blank lines on the "SharePoint" Tab (worksheet). # is equal to row number.

    Basically "SharePoint" Tab (worksheet) should just be a list of all rows where AJ# <= 2.

    Reply
    • Mynda Treacy

      December 5, 2018 at 9:59 am

      Hi Svea,

      If you want to extract a set of values that match a criteria then I’d use a PivotTable with a filter for values AJ# <=2. If you need help setting it up, please post your question and sample Excel file on our forum where we can help you further.

      Mynda

      Reply
  159. Jeremy

    November 29, 2018 at 11:17 am

    Hi there,

    I am trying to have a cell automatically fill in a choice of two phrases depending on what the value is in another field.

    So, here is what I am trying to achieve:

    if P10 = Yes, then I want S10 to say “Required” as well as
    if P10 = No or Unsure, then I want S10 to say “check requirements with provider”

    Reply
    • Catalin Bombea

      November 29, 2018 at 8:57 pm

      Try this formula:
      =If(P10=”Yes”,”Required”,”check requirements with provider”)
      For any value other than Yes, the formula will return “check requirements”
      If you want those strict cases -No,Unsure, try this:
      =If(P10=”Yes”,”Required”,If(OR(P10=”No”,P10=”Unsure”),”check requirements with provider”),”Other case”)

      Reply
  160. Rob

    November 28, 2018 at 2:09 pm

    I have a spreadsheet that shows occurrence on a given date

    Column A is the date, Column B shows “1” if there is an occurrence on that date EG

    25/11/2018 1
    26/11/2018
    27/11/2018
    28/11/2018 1
    29/11/2018
    30/11/2018 1

    What I am trying to do is take the current date that the spreadsheet is open (that’s the easy part (today()) and look back over the total occurrences from the previous 90 days.

    to put it simply what is the total number of occurrences in the last 90 days

    Reply
    • Philip Treacy

      November 28, 2018 at 2:22 pm

      Hi Rob,

      Occurrence of what?

      Are you trying to count how many times the workbook has been opened on a particular day? And then total that for the last 90 days?

      Regards

      Phil

      Reply
  161. Kamlesh Dhuria

    November 27, 2018 at 5:39 pm

    I have to calculate who is L1 from three columns also if any duplicate values are there then the result should show FALSE

    e.g.

    Column A Column B Column C Result: FALSE
    1 2 2

    Column A Column B Column C Result: Column A
    1 2 3

    Reply
    • Catalin Bombea

      November 28, 2018 at 11:31 pm

      Hi Kamlesh,
      Question is: what L1 means for you? The smallest value?
      Try this formula:
      =IF(SUMPRODUCT(COUNTIF(A1:C1,A1:C1)*1)>3,FALSE,SUMPRODUCT((A1:C1=MIN(A1:C1))*COLUMN(A1:C1)))
      It will return the column number of the smallest value.

      Reply
  162. mohan

    November 24, 2018 at 10:45 pm

    find a calum this & if you found this count this as 1 & count this as 0.5 in excel

    Reply
    • Philip Treacy

      November 25, 2018 at 11:04 am

      Sorry Mohan it’s not clear what you want. If you can open a forum post and include a workbook with data that will help us to help you.

      Regards

      Phil

      Reply
  163. Debojyoti Ganguly

    November 24, 2018 at 5:15 pm

    Hi,

    I Need a formula as below mentioned.

    I have total material lifting date and after completion of work old material submission date. How can I get the ageing between them and also in that cases where I have not submitted the old material but lifted new material.

    Reply
    • Catalin Bombea

      November 24, 2018 at 5:27 pm

      Hi Debojyoti,
      Can you please upload a sample file with your data and expected results? Hard to imagine your data structure. Use our forum to upload (create a new topic after sign-up).

      Reply
  164. Kat

    November 23, 2018 at 1:08 am

    Hi,

    I need a formula that is a mix of IF, OR and AND. Can you help?

    I need to stack a formula that says if column A = A or B and column C > year 2015 and column D has a value greater than 500, Column E must also contain the text “ABCD”, “EFG” OR “XYZ”. if this criteria is met “Include” if not then “exclude”. Is this level of criteria possible? I can’t get the criteria to meet it. Thanks

    Reply
    • Catalin Bombea

      November 23, 2018 at 3:52 pm

      Hi Kat,
      try this in column F:
      =IF(AND(OR(A2=”A”,A2=”B”),YEAR(C2)>2015,D2>500,OR(ISNUMBER(SEARCH(“ABCD”,E2)),ISNUMBER(SEARCH(“EFG”,E2)),ISNUMBER(SEARCH(“XYZ”,E2))),”include”,”exclude”)

      Reply
  165. Liz

    November 22, 2018 at 12:50 pm

    I’m trying to get a formula to add 18 months or 36 month based on a word in a cell. For example,
    If cell A2 has the word “Maintenance”, “Pressroom”, or “S/R” in it, I need the date in C2 to add 18 months to the date in B2, but if A2 has any other text, then C2 needs to add 36 months to the date in B2.

    I have 3 departments that get reimbursed every 18 months, and all other departments get reimbursed every 36 months. How can I set up a formula to note when their time comes for another reimbursement?

    Reply
    • Mynda Treacy

      November 22, 2018 at 9:41 pm

      Hi Liz,

      Try this:

      =IF(OR(A2="Maintenance",A2="Pressroom",A2="S/R"),EDATE(B2,18),EDATE(B2,36))

      Mynda

      Reply
  166. Shelly

    November 22, 2018 at 12:15 am

    Hi, I have a price in C2, that affects the rest of the sheet, if it goes up or down in price, what would be the formula? So that the other colums add or minus the £10 difference. Many thanks

    Reply
    • Philip Treacy

      November 22, 2018 at 9:27 am

      Hi Shelley,

      It’s not really clear what you want. I don’t know what formula you want in other cells. What $10 difference? Are you adding/subtracting 10 from a total somewhere? In which case wouldn’t you just use -10 or + 10?

      Maybe if you open a forum post and supply some sample data in a workbook it will be clearer.

      Regards

      Phil

      Reply
      • Shelly

        November 22, 2018 at 7:10 pm

        Hi Phil

        Sorry. I will try to explain better. The LME @ 4300 (in C2) can vary by £10; so I’m looking at when this changes happens, it will change the values C7:C20 C23:C30, and a few others across the book.

        LME 4300

        COPPER FRED CODE £
        DRY BRIGHT WIRE (free from grease)
        CLEAN ELECTRO (free from tinned ends)
        CLEAN COPPER TUBE (free paint, solder)
        HARD DRAWN WIRE (free from cd)
        GREASY BRIGHT WIRE (max 5% greasy wire)
        98% COPPER (HEAVY COPPER)
        NO 2 COPPER (94%)
        CLEAN COPPER TANKS (no scale/elements/brass 92% min)
        BRAZIERY (88% min / no free brass)
        LEAD WASHED RADS (78% min)
        CLEAN PYRO CABLE(min 66%)
        PVC PYRO CABLE (min 56%)
        ELEMENTS (min 52%)

        Reply
        • Philip Treacy

          November 23, 2018 at 7:31 am

          Hi Shelly,

          I’m afraid it’s still not quite clear, that’s why I suggested you open a post on the forum and include your workbook with data, it’s much easier to understand when we can open a workbook.

          One thing I don’t get is that you say the value in C2 changes various other cells, but how do you want it to change these cells? Where does IF fit into this situation? Haven’t you already written the formulae that determine the value of C7:C20, C23:C30 etc?

          Regards

          Phil

          Reply
  167. Jim Baker

    November 20, 2018 at 2:21 pm

    Hello,

    Need an hour formula to separate 40 hours from total hours or show total hours if >40.
    example: Total hours in Cell A1 are 72. In Cell A2 I want to say if A1 is >40 than 40. If A1 is =to or 40,”40″)???

    Reply
    • Philip Treacy

      November 20, 2018 at 2:54 pm

      Hi Jim,

      I’m not really clear on the logic of what you want.

      show total hours if >40 is different to if A1 is >40 than 40

      and I don’t know what this means

      If A1 is =to or 40,"40")

      maybe try this in A2 and adjust it to suit

      =IF(A1>40,A1,40)

      Phil

      Reply
  168. Kei

    November 20, 2018 at 11:17 am

    Hello ,
    What is the formula if cell A is Negative I want to appear it to zero and if its positive i want it to multiply by 30%?

    Thank You.

    Reply
    • Philip Treacy

      November 20, 2018 at 11:50 am

      Hi Kei,

      If you are testing a value in A1, then in A2 you can have

      =IF(A1<0,0,A1*0.3)

      Regards

      Phil

      Reply
  169. Damon Ak

    November 20, 2018 at 4:44 am

    I need a formula that allows me to assign a value to one box (i.e. D2), based on the value of another box (i.e. E2).

    If E2 is between 0-10, D2 = 100
    If E2 is between 11-20, D2= 75
    If E3 is between 21-30, D2 = 50
    If E3 is between 31-40, D2 = 25
    If E3 is between 41-50, D3 = 0

    Anyone can help me with that?

    Reply
    • Mynda Treacy

      November 20, 2018 at 9:50 am

      Hi Damon,

      You should use this VLOOKUP on a sorted list technique for this.

      Mynda

      Reply
    • Kent E Durbin

      November 22, 2018 at 5:29 am

      I want formula for IF D15 is more than 5 and less than 10 then “GOOD”

      Reply
      • Philip Treacy

        November 22, 2018 at 9:22 am

        Hi Kent,

        =IF(AND(D15>5,D15<10),"GOOD","BAD")

        You haven't said what you want the result to be if D15 does not meet your criteria, so I've chosen to have BAD in the cell in such circumstances.

        Regards

        Phil

        Reply
  170. Leon Coetzee

    November 14, 2018 at 7:49 pm

    How do you calculate the value of c1 using a reference in a1, in other words, excel must calculate the sum in c1 if reference in a1 is “TAM”

    Reply
    • Mynda Treacy

      November 14, 2018 at 8:48 pm

      Hi Leon,

      =IF(A1=”TAM”,C1,0)

      Mynda

      Reply
  171. K.RAJAGANAPATHY

    November 14, 2018 at 5:58 pm

    sir, i want a formula for in excel to IFAND FORMULA OR VLOOKUP

    H.R.A TO basic pay of 6% of subject to and minimum Rs-1400,and maximum Rs-2400

    for example
    basic pay 10,000 result 1400
    basic pay 20,000 result 1400
    basic pay 30,000 result 1800
    basic pay 40,000 result 2400
    basic pay 50000 result 2400

    Reply
    • Mynda Treacy

      November 14, 2018 at 8:52 pm

      Try this:

      =IF(A1<23333,1400,IF(A1>40000,2400,A1*0.06))

      Mynda

      Reply
  172. Ayla

    November 13, 2018 at 3:58 am

    I am creating a form to request a guided tour. We offer our tours at different times depending on the day of the week. I have the Microsoft Date Picker on the form so that users may pick their date on a calendar and I would like another cell to show a certain drop down list if the date they chose is a Tuesday and another drop down list if the date they chose is a Wednesday, etc. How can I do this?

    Reply
    • Philip Treacy

      November 13, 2018 at 10:09 am

      Hi Ayla,

      If you can open a post on the forum and supply your code it’ll be easier for us to help you find a solution.

      Regards

      Phil

      Reply
  173. Sejohn

    November 11, 2018 at 11:46 am

    . PAYE is calculated as a flat rate of 8% on any amount in excess of $25,000. No PAYE is deducted if the salary is less than or equal to $25,000

    i need a formula to help me work out if an amount is greater than 25,000 add 8% of the person salary if not no paye is deducted

    so lets say my salary is in A1 and my qualifying income is in B1 then If(B1 is greater than A1 then 8% of A1 is Added to B1 formula

    Reply
    • Catalin Bombea

      November 11, 2018 at 3:25 pm

      Hi Sejohn,
      Try this in C1:
      =IF(A1>25000,B1+B1*0.08,B1)
      Or:
      =B1+IF(A1>25000,B1*0.08,0)

      Reply
  174. Zana

    November 6, 2018 at 3:52 am

    Hello all,

    Im in this situation when i need to know when someone made a modification in the spreadsheet, meaning i need the date when the cell containment has been change.
    I was trying with =IF(D2=”yes”, today()) … but is not working as in refreshing the date every time i open the spreadsheet.
    And once i find the working formula, i would love to block it so no one could delete it or anything like that.
    its for having control of the picking dates for specific parts.

    Thank you so much!

    Reply
    • Catalin Bombea

      November 6, 2018 at 2:55 pm

      Buna Zana,
      Only a custom code will be able to do that, as you noticed the formulas will update each time.
      Can you upload on our forum a sample file with details of what you’re trying to achieve?
      Catalin

      Reply
  175. Toni

    October 31, 2018 at 8:39 am

    I need help again. I’m stuck again it seems so easy. If and And formula. If sales are greater than 20,000 and have submitted a report (columns say yes or no) than they get 5000 dollars bonus if not 0.

    Reply
    • Philip Treacy

      November 1, 2018 at 1:08 pm

      Hi Toni,

      Try understanding each function on its own before combining them.

      So if Sales > 20000 then a bonus of 5000 is awarded. Otherwise 0 bonus. Assuming the value for Sales is in A1, the IF for this is

      =IF(A1>20000,5000,0)

      Looking at AND, it returns True or False. It returns True if the conditions you give it are both True, otherwise you get False. So, with either a yes or no in B1 you would write your AND like this

      =AND(A1>20000, B1=”yes”)

      This will give you True if both A1 is greater than 20000 AND B1 contains the text “yes”

      Now all you have to do is insert that AND into the IF we wrote earlier

      =IF(A1>20000,5000,0)

      becomes

      =IF(AND(A1>20000, B1=”yes”),5000,0)

      Please note that checking for text like this is not case sensitive. “yes” is the same as “Yes” or “YES”.

      Regards

      Phil

      Reply
  176. Jay Soto

    October 30, 2018 at 10:34 pm

    Looking for date formula calculation:

    A1 cell is Widget 1, 2, 3, 4
    A2 cell is date – I want to add 17 months if Widget 1, 2, and 3 are selected – if Widget 4 is selected I want to add 36 months.

    Thank you for your time

    Jay

    Reply
    • Mynda Treacy

      October 31, 2018 at 1:54 pm

      Hi Jay,

      Try this:

      =IF(A1=4,EDATE(A2,36),EDATE(A2,17))

      Mynda

      Reply
  177. Toni

    October 30, 2018 at 1:29 pm

    I need help with Or function. Employees receive 5000 bonus if their sales in 1St quarter or 2ND quarter are greater than 30,000. If not 0 bonus.

    Reply
    • Philip Treacy

      October 30, 2018 at 8:04 pm

      Hi Toni,

      Without knowing how your data is set out I am assuming that Q1 Sales are in A1 and Q2 Sales are in B1

      =IF(OR(A1>30000,B1>30000),5000,0)

      I’d use another cell to store the threshold value of 30000 though, so it’s easier to adjust as needed. Assuming you store the value 30000 in D1 this would give you

      =IF(OR(A1>D1,B1>D1),5000,0)

      Regards

      Phil

      Reply
  178. MIHA

    October 29, 2018 at 8:42 pm

    HELLO

    I NEED A FORMULA TO SHOW ME A TEXT IF ONE OF TWO STATMENTS ARE TRUE, SOMETHING LIKE THIS IF(OR(W3<0,W3=0),"TEXT"," ")

    I TRIED TO USE IT LIKE THAT AND IT SHOWS ERROR

    WHAT AM I DOING WRONG?

    THANKS

    Reply
    • Philip Treacy

      October 30, 2018 at 12:12 pm

      Try this

      =IF(W3<=0,"TEXT"," ") Regards Phil

      Reply
  179. Prabhakaran G

    October 26, 2018 at 4:50 pm

    I want if formula in excel for Electricity bill calculate

    That the concept is

    0 to 100 units is free of cost

    101 to 150 units is consumed, calculate amount Rupees 1.50/unit

    and above 150 units consumed, calculate Rupees 2/unit

    How is the if formula for Ms-Excel calculate , please send to my email

    (0 to 100 Free, 100 to 150 *1.50, above 150 *2)

    Reply
    • Mynda Treacy

      October 26, 2018 at 8:05 pm

      Hi, I think this is what you’re after:

      =IF(A1<101,0,IF(A1<150,(A1-100)*1.5,IF(A1>150,(A1-100)*1.5+(A1-150)*0.5)))

      Mynda

      Reply
  180. AJ

    October 18, 2018 at 6:11 am

    Hello I need help making a formula for an Date Tracker. Essentially what I want to do is this:

    Say:
    A = Date of Training
    B = Today’s Date
    C= Color Red, Green, or Yellow.

    Then I want a formula for multiple days that says, B is 365 days (or a year) passed A, then C will be red. Then if B is within 60 Days of 365 Days of B, then C will be yellow. Then if B is not passed 365 or within 60 days of 365 for A then C will be Green.

    I wanted if the date is overdue a year to go red, within 60 days of that year to be yellow, and if they are not meeting those two then they are green

    Reply
    • Catalin Bombea

      October 22, 2018 at 2:13 pm

      Hi AJ,
      Assuming that column C will contain the number of days between column B and column A, select the range from column C and insert a conditional formatting rule (Home tab, Styles section, Conditional Formatting – choose Icon Sets, any icon set you like.). In the next window, you will need to press the button Reverse Icon Order, they usually start with green, in your case must start with red.
      The rules should be set as follows:
      Red: when value is >= 365, type Number
      Yellow: when < 365 and >= 60, type Number
      Green: when <60

      Reply
  181. mangesh

    October 16, 2018 at 3:36 pm

     Three products out of the 6 should achieve 100% of the unit wise target
    – If achieved, 3% of the rupee value of any 3 out of 6 products
    I want conditional formula of this condition

    Reply
    • Philip Treacy

      October 17, 2018 at 8:42 am

      Hi Mangesh,

      Please post this on the forum with a sample workbook. Its’s too hard to work out exactly what you want without seeing data and knowing what cells to reference.

      Regards

      Phil

      Reply
  182. JB

    October 9, 2018 at 9:52 pm

    Hi, i have a sheet with a column (Q) thats adding with the basic sum formula. =Sum(K3+Q2) and it goes down in same column with same formula no more than 110 rows per sheet.
    i made another column (R) and i want to use the =if(or formula. and im stuck.. i made another Column (Y) with starting value of 120 going down the column from Row2 down to Row 23. the are in increments of 120. so starts at 120 in Y2 end at 2640 Y23.

    this is what i need help with. I would enter the formula in R3. If the value of Q3 is equal or greater than 120 but equal or less than 240, then Subtract Q3 – 120 , Or if its equal or greater than 241 but Equal or less than 360, then subtract Q3-240. or if its none add Q2+k3

    and i would continue the formula until i reach 2640. Column Y signifies Hours and column Q as well

    the purpose of this is to identify my operations by every 120 hours to reset and start a new day.

    Reply
    • Mynda Treacy

      October 10, 2018 at 9:42 am

      Hi JB,

      Try this:

      =IF(Q3>360,Q2+K3,IF(Q3>240,Q3-240,IF(Q3>=120,Q3-120,Q2+K3)))

      If that isn’t what you’re after please post your question and a sample Excel file on our forum so we can better understand your question.

      Mynda

      Reply
  183. Vir

    September 26, 2018 at 10:07 pm

    in Column A, there are Buy and Sell.
    Column B is Qty like 25,50,100,150
    Column C is amount….like 500.00, 535.00, 600.00

    now i wants to put the formula in “C” Column that if Buy then figure should be in Mines symbol (-500.00, -535.00, -600.00)
    If Sell then it should be in + figure

    how can i do?
    plz suggest.
    Thanks

    Reply
    • Mynda Treacy

      September 27, 2018 at 12:13 pm

      Hi Vir,

      Try this in column D (note: you don’t say how column C values are arrived at, so all I can do is convert them to +/- in column D):

      =IF(A2="Buy",-C2,C2)

      Mynda

      Reply
  184. Zackary Easley

    September 20, 2018 at 3:37 pm

    What is the answer?!?!?! AHHHH

    In the embedded Excel workbook below insert a formula (in the grey cells in column E) that calculates the bonus due for each salesperson. A $500 bonus is paid if a salesperson meets either target in cells C24 and C25, otherwise they earn $0 bonus.

    Reply
    • Mynda Treacy

      September 20, 2018 at 3:45 pm

      Hi Zackary,

      The answer is in the workbook you can download for this tutorial.

      Mynda

      Reply
  185. Loretta

    September 15, 2018 at 12:44 am

    Hi, I am trying to write a formula in conditional formation that evaluates a cell for >=and=90, F2<=100,"Excellent"))) but I have 6 evaluations for the same cell with different evaluation and a return different text, very good or good…..I understand I need to put the six different formulas together but I cannot seem to make it work.

    Reply
    • Mynda Treacy

      September 15, 2018 at 7:19 am

      Hi Loretta,

      A better function to use is VLOOKUP on a sorted list as described here.

      Mynda

      Reply
  186. Dildar

    September 5, 2018 at 6:03 pm

    Hi,
    I need to put Note given below into this formula.
    =IF(AT1350=400000,0,IF(AT1350<=800000,1000,IF(AT1350<=1200000,2000,IF(AT1350<=2400000,(AT1350-1200000)*5%,IF(AT13504800000,(AT1350-4800000)*15%+(300000)))))))

    Note,{ Provided that where the taxable income exceeds Rs. 800,000/- the Minimum tax payable shall be Rs. 2,000/-

    Reply
    • Mynda Treacy

      September 5, 2018 at 8:58 pm

      Hi Dildar,

      You can use the N function to add a comment/note to a formula e.g.:

      =IF(AT1350=400000,0,IF(AT1350<=800000,1000,IF(AT1350<=1200000,2000,IF(AT1350<=2400000,(AT1350-1200000)*5%,IF(AT13504800000,(AT1350-4800000)*15%+(300000)))))))+N("Provided that where the taxable income exceeds Rs. 800,000/- the Minimum tax payable shall be Rs. 2,000/-")

      Mynda

      Reply
  187. Robson

    September 4, 2018 at 10:40 am

    I am trying to write a formula where if A2=65 and A2<75, the word "pass" appears on the cell, if A2<75, the cell is blank.

    Reply
    • Philip Treacy

      September 4, 2018 at 10:55 am

      Hi Robson,

      If A2 = 65 then it must be less than 75 too. And if A2 < 75 it can't be both 'pass' and blank? The logic there isn't quite right? Regards Phil

      Reply
  188. MONARK VADADORIYA

    September 1, 2018 at 9:57 pm

    HELP ME :

    if cell A3 is 0-50, cell B2 is 10-100 than cell B3 is 80

    cell B5 is 20 as per A3, cell C5 is 60 as per cell B2 than cell cell D3 is 80 autocalculate by formula

    than let me know which formula I use for cell D3

    Reply
    • Catalin Bombea

      September 3, 2018 at 1:46 pm

      Hi Monark,
      Not sure I follow your logic, can you please upload a sample file on our forum (create a new topic after sign-in), with more examples of how the calculation should be? It will be much easier to understand your situation and provide a functional solution.Thanks for understanding.
      Catalin

      Reply
  189. Dwarakesh A

    August 27, 2018 at 3:52 pm

    If(A1>=B1:B10,1,0)

    Please tell if this formula works, if not give me any alternate

    Reply
    • Catalin Bombea

      August 27, 2018 at 4:34 pm

      Hi Dwarakesh,
      Depends on what you need: the value from A1 must be greater than all individual values from B1:B10 to return 1?
      I yes, you can try this one:
      =SUMPRODUCT(1*(SUM((A1>B1:B10)*1)=(ROWS(B1:B10))))

      Reply
  190. Derok

    August 2, 2018 at 12:22 pm

    I am data in having 6 columns and many rows. I want to check if the date (column B) is less than today, then highlight the row. Rows will be getting updating.

    I am using conditional formatting with formula =$B2<TODAY() to highlight the rows (A2:F2000). This is working but this is highlighting empty rows also. How to avoid highlighting the empty rows?

    Reply
    • Catalin Bombea

      August 2, 2018 at 1:33 pm

      Just add another condition, B2 should not be empty:
      =AND($B2<>“”,$B2

      Reply
  191. Andy

    July 30, 2018 at 8:54 pm

    This one doing my head in. I am sure its simple

    For 1 – 50 widgets you buy you get 20 wingdings for free.

    however for every additional widgets above 50 (to next 50) , you get an additional 5 wingdings and so on

    so buy 49 widgets get 20 wingdings

    buy between 51 and 100 widgets you get 25 windings
    buy between 101 and 150 you get 30 wingdings, 151 – 200 widgets, 35 wingdings and so on.

    Reply
    • Catalin Bombea

      July 31, 2018 at 1:29 pm

      Hi Andy,
      Yes, it’s simple, try this one:
      =IF(A1<=0,0,20)+INT(A1/50)*5

      Reply
      • Andrew McIntosh

        August 21, 2018 at 3:52 pm

        Slight modification on the above;

        Less than or equal to 600 widgets you get 0 wingdings
        = to and above 651 you get 30 wingdings
        than for every additional 50 widgets you get 2 wingdings
        eg 651 widgets get 30 wingdings
        704 widgets get 32 wingdings
        810 widget get 36 windings

        Thanks

        Reply
        • Philip Treacy

          August 21, 2018 at 4:27 pm

          Hi Andrew,

          You don’t say what to do if the number is between 600 and 651?

          Otherwise, you can use this

          =IF(A1>=651,30+(INT((A1-650)/50)*2),0)

          Regards

          Phil

          Reply
  192. Moshood Oladimeji

    July 28, 2018 at 6:05 am

    I have an issue with the table below

    Days of wk Time In Time Out Total Hrs Time In Time Out Total Hrs
    Wed 12/28 3:30:00 PM 7:00:00 PM 7:00:00 PM 11:15:00 PM 7.75
    Thu 12/29 12:00:00 AM 4:30:00 AM 8:00:00 AM 7:00:00 PM 15.50
    Thu 12/29 7:00:00 PM 4:00:00 AM Nil Nil 9.00
    Fri 12/30 8:30:00 AM 7:00:00 PM 7:00:00 PM 4:30:00 AM 20.00
    Sat 12/31 8:40:00 AM 1:45:00 PM Nil Nil 5.08
    Tue 1/3 8:40:00 AM 7:00:00 PM 7:00:00 PM 5:00:00 AM 20.33
    Wed 1/4 8:55:00 AM 7:00:00 PM 7:00:00 PM 4:45:00 AM 19.83
    Thu 1/5 10:35:00 AM 7:00:00 PM 7:00:00 AM 2:00:00 AM 15.42
    Fri 1/6 9:10:00 AM 7:00:00 PM 7:00:00 PM 3:40:00 AM 18.50
    Sat 1/7 9:00:00 AM 3:00:00 PM Nil Nil 6.00

    1. How to calculate the total hours in the last column as it is shown ( take note how the figures are been written)

    2. How do I remove the seconds aspect of the time i mean how do i change hh:mm:as to hh:mm

    Reply
    • Catalin Bombea

      July 30, 2018 at 7:41 pm

      Hi Moshood,
      Can you please upload a sample file on our forum? It will be easier to work on your data, date calculations are sensitive.
      Thank you
      Catalin

      Reply
  193. marl

    July 18, 2018 at 3:49 pm

    am trying to figure how to calculate this question on excel. please help if you can.

    Pulitzer Photography pays young people to approach people in major department stores and offer them a free photograph if they come in to have their portrait taken.

    They are paid $50, plus a commission of 2% of any sales of additional photos to the client up to $100, 5% of additional sales above $100 to $200, and 10% of additional sales above $200.

    Reply
    • Mynda Treacy

      July 19, 2018 at 9:06 am

      Hi Marl,

      Let’s assume your sales value is in cell A1, then you can use this formula:

      =50+IF(A1>200,A1*0.1,IF(A1>100,A1*0.05,A1*0.02))

      Mynda

      Reply
  194. Mark

    July 13, 2018 at 6:55 am

    I have an issue.

    I am setting up a chart based on units of run time for when it is due for a service and the cost. The example I have is:
    0-2000 – cost $600 to maintenance
    2001-4000 Cost $1750 maintenance and service
    4001-6000 Cost $600 maintenance

    I have the data as
    C D E
    1 0 2000 600
    2 2001 4000 1750
    3 4001 6000 600

    Entry is:
    A1 = 689 B1 =vlookup( A1,C1:E3,3,True) This displays the 600 total – all good
    A2= 1378 B2 =vlookup( A2,C1:E3,3,True) This displays the 600 total – all good
    A3= 2067 B3 =vlookup( A3,C1:E3,3,True) This displays the 1750 total – all good
    A4= 2755 B4 =vlookup( A4,C1:E3,3,True) This displays the 1750 total – here is where I need help. How can I get it reconize that it had service at the 2067 mark and cost would go back to 600?

    Can this be done or do I need to set it up differently?

    Reply
    • Catalin Bombea

      July 13, 2018 at 2:34 pm

      Hi Mark,
      A4-2755 fits in the 4000-6000 range, with 600 cost, same as A3-2067, why are you saying that one should be 1750 and the other 600?
      =INDEX({0,600,1750,600},MATCH(A1,{0,2001,4001,6001},1)) should give you the correct values.

      Reply
      • Mark

        July 13, 2018 at 10:27 pm

        Thank you!
        I am saying that when the run time for the machine hits say 2001, there will be additional maintenance needed, but there isn’t additional maintenance needed until 6001 so the cost would go down to 600 for routine upkeep. I wanted a way that once it used the 1750 value it could go to the next cost which would be 600… if that makes sense.

        Reply
        • Catalin Bombea

          July 14, 2018 at 3:41 pm

          So between 2001 and 6001 there is no additional cost? What’s the 4001 limit for then?
          I think you should provide a sample file with manual results, to clarify the problem, at this point it’s not very clear.
          Create a new topic on our forum after sign-in and upload the sample file with a clear description and manual examples of how you need the results to be, there should be a solution.

          Reply
  195. Tonia

    July 6, 2018 at 6:36 pm

    Hi,
    Great article. I have a question. I have =A5-B5 cell to get C5 Value. But if I have no value in say B5, in C5 cell, I get 1. E.g A5=1, B5=0 , i get 1 in C5.

    How do I write an IF statement to Say “none” in C5. If A5 has value and B5 has no value. say 1 – 0 = none.

    Thanks

    Reply
    • Catalin Bombea

      July 7, 2018 at 3:19 pm

      Hi Tonia,
      Try this in C5: =IF(B5=0,”none”,A5-B5)

      Reply
  196. Nakita

    July 5, 2018 at 12:47 pm

    Hiya, I am currently doing a savings tracker based on countdown weeks to our wedding and how much money has been put into savings. Eg currently H32 has 21 weeks till our wedding written as 21 and H 33 has our goal left to save in those 21 weeks currently at 10,500. Each week we save $500. What I would like is that whenever I subtract a week so 21 to 20, i would like the savings box to move from $10,500 to $10,000 (subtract $500). Could you please help me with a code for this? There is not text in the grid, just the numbers 🙂

    Reply
    • Catalin Bombea

      July 7, 2018 at 3:36 pm

      Hi Nakita,
      Can you please upload a sample file to our forum with a manual example of how the result should be? Hard to visualize the problem, a sample file will be a great help for us to understand hat you need.
      Create a new topic after sign-in to upload the file.
      Thank you

      Reply
  197. Javahar Babu

    July 4, 2018 at 3:04 am

    Hi Mynda, i need your help on below

    155, 190, 250 and 300 value. in which these values should check 210 and should give the values as 155 – L1, where as it is less than 210, 190 – L2 it is next less vlaue when campared to 210, 250 – L3 it is bigger than 210 and finally 300 – L4. i have tried conditions in excel but i couldn’t through.

    Reply
    • Catalin Bombea

      July 4, 2018 at 11:21 pm

      Hi Javahar,
      try: =INDEX({“L1″,”L2″,”L3″,”L4”},MATCH(A1,{155,190,250,300},1))
      Catalin

      Reply
  198. vaibhav singh

    June 5, 2018 at 3:07 pm

    i have 10+25 in one row and 13+28 in second row. I want that the total will come in third row but don’t want to use “=” before 10+25 and 13+28 as this data needs to show. Pls help

    Reply
    • Mynda Treacy

      June 6, 2018 at 11:18 am

      Hi Vaibhav,

      If you want to add values then they need to be entered as values/numbers for the formula in the third row. Without the = sign they are text and you cannot add text in your third row and return a value/number. In other words, it’s not possible. Perhaps you can put the text as desired in the column beside the proper values and then reference the correct values/numbers for your formula result in the third row.

      Mynda

      Reply
  199. Kunjan Jain

    June 4, 2018 at 10:33 pm

    My query is : say a cell contain pan no. AAACA1234A
    whats the formula to get the result if fourth letter in the pan is “C” than generate result “company” and if letter is not “C” get result “non company”

    Reply
    • Philip Treacy

      June 5, 2018 at 9:14 am

      Hi,

      If your data is in A1 this is what you need

      =IF(MID(A1,4,1)=”C”,”Company”,”Non Company”)

      Regards

      Phil

      Reply
    • Kunjan Jain

      June 5, 2018 at 6:43 pm

      Thanks a lot PHIL TREACY !!!!!!

      Reply
      • Philip Treacy

        June 6, 2018 at 8:38 am

        You’re welcome.

        Reply
  200. Faisal Shahzad

    June 4, 2018 at 4:35 am

    Hi,
    I need help to all of you
    actually i am creating a incentive calculator for my Sales team
    according to our incentive policy
    if a sales person achive his monthly target then the total unit multiply with the rate,

    here is 2 different situation apply

    1st situation
    if a person achive his monthly target >100%
    for example
    a person sale a product 110unit
    100 unit is a monthly target
    the 100 unit multiply with 100% slab and rest of the 10 unit multiply with >100% or 110% slab.
    the slab are also mention here
    <80% <89% <99% 100% 110%
    56 62.3 69.3 70 87.5 105

    kindly suggest me some better option for the calculating of these incentive

    Reply
    • Philip Treacy

      June 4, 2018 at 11:07 am

      Hi Faisal,

      Can you please open a topic on the forum and supply a workbook with sample data.

      Thanks

      Phil

      Reply
  201. Joanna

    May 30, 2018 at 12:37 pm

    Hello everyone,

    Desperate need of help as it is driving me insane. I am sure to myself that my formula is correct but not working for me at all so not so sure anymore.

    I want cell F5 to have the value of 15 if Cell D5 and E5 have greater value than 1. The formula I am using is: =IF(AND(D5>=1,E5>=1),”15″,0)

    Reply
    • Philip Treacy

      May 30, 2018 at 2:51 pm

      Hi Joanna,

      This formula works fine for me. You haven’t said what error you are getting and I don’t know what data you have in D5 and E5, is it text or numbers?

      In your formula you have 15 in quotes “15” which means it is text, not a number. Nothing necessarily wrong with that if you want F5 to contain text though, but best not to store numbers as text.

      Also, you have written ‘I want cell F5 to have the value of 15 if Cell D5 and E5 have greater value than 1’ but your formula is testing if the values in D5 and E5 are greater than or equal to 1.

      If you can’t figure it out please post on our forum with your workbook so we can see the data.

      Regards

      Phil

      Reply
  202. Laura

    May 26, 2018 at 10:18 pm

    Hi there,
    I need some help with a simple formula I have to create. The description is the next one:

    “If Stephanie has sales less than 600 in October, less than 750 in November, or less than 750 in December, the text No Bonus is shown, and if not, the text Bonus is shown.”

    The table is this one:

    ” A B C D E
    1. bonus entitlement___________________________________
    2. sales person Oct.Sales ¦ Nov.Sales ¦ Dec.Sales¦ Bonus

    3. Stephanie 637 829 726
    4. Paul 819 939 826
    5. Adam 512 721 637
    6. Kaylee 728 843 945
    7. Thomas 478 526 456
    8.

    I would really appreciate if you could help me cause I am really stock and I have no idea about excel formulas.
    As far as I could get is this: =IF(A3<600
    Which I dont even know if I am going well or not.

    Kind regards

    Laura.

    Reply
    • Philip Treacy

      May 28, 2018 at 9:39 am

      Hi Laura,

      For sales targets that have to be met every month :

      =IF(OR(B2<600,C2<750,D2<750),"No Bonus","Bonus")
      

      This is assuming the values for sales start in cell B2.

      Regards

      Phil

      Reply
  203. NICOLE MCGUINNESS

    May 24, 2018 at 1:46 pm

    i am trying to do a spreadsheet using sales – G10-14 have the profit in them, i need to then do a bonus column (K) – I need to do an IF formula selecting the highest profit and show they get a $2000 bonus. this is really getting me stuck!!

    Reply
    • Catalin Bombea

      May 24, 2018 at 3:04 pm

      Hi Nicole,
      Can you please upload a sample file with some examples of how the results should be?
      Use our forum to upload. (create a new topic after sign-up)
      Regards,
      Catalin

      Reply
      • Nicole McGuinness

        May 24, 2018 at 3:43 pm

        Have uploaded now on the forum

        Reply
        • Catalin Bombea

          May 24, 2018 at 7:42 pm

          Great, see you there.

          Reply
  204. hattan

    May 23, 2018 at 4:27 pm

    I want to write this formula
    If K: K=”in process” then calculate the number in E:E in E 300 otherwise don’t

    Reply
    • Catalin Bombea

      May 24, 2018 at 2:55 pm

      Hi Hattan,
      What “calculate the number in E:E” means for you?
      If it’s a sum, you can try this: =SUMIF(K:K,”in process”,E:E)
      Cheers,
      Catalin

      Reply
  205. satish

    May 19, 2018 at 5:48 am

    How to Add if value is

    17/10
    03/5
    13/11

    Total is
    33/26

    Please help to resolve it and share formula

    Reply
    • Catalin Bombea

      May 19, 2018 at 3:25 pm

      Hi Satish,
      That’s not a value, it’s a text.
      Split this into 2 columns, create the total for each column, then create the text string combining the 2 column totals.

      Reply
  206. Tracy

    May 10, 2018 at 3:54 am

    =if(and(B:B=”RR”,=A5:A300))

    In other words, if Cells in B have RR in them, We want to count A5 thru A300. What am I doing wrong?

    Reply
    • Mynda Treacy

      May 11, 2018 at 11:31 am

      Hi Tracy,

      You don’t need IF, you need COUNTIF like this:

      =COUNTIF(B:B,”RR”)

      Mynda

      Reply
  207. Faisal

    May 7, 2018 at 3:55 am

    Sales Target 100% 90-99 89-80 70-79 <70
    <80% 2.10% 1.9% 1.7% 1.3% 1.1%
    <89% 2.40% 2.2% 1.9% 1.4% 1.2%
    <99% 2.70% 2.4% 2.2% 1.6% 1.4%
    100% 3.00% 2.7% 2.4% 1.8% 1.5%
    106% 4.50% 4.1% 3.6% 2.7% 2.3%

    i want to create a incentive sheet
    if sales target is less then 80% and avg selling price is <70% then value cell*with 1.1

    i am trying to calculate via this formula but the result shown false

    =IF(AND(E8<80%,E9<70%),D12*N15,IF(AND(E8<90%,E9<70%),D12*N16))
    plz correct it

    Reply
    • Catalin Bombea

      May 9, 2018 at 9:57 pm

      Hi Faisal,
      The data table should look like this, in range A1:F7:
      ST\SP 0% 70% 80% 90% 100%
      0% 0% 0% 0% 0% 0%
      80% 1.10% 1.30% 1.70% 1.90% 2.10%
      89% 1.20% 1.40% 1.90% 2.20% 2.40%
      99% 1.40% 1.60% 2.20% 2.40% 2.70%
      100% 1.50% 1.80% 2.40% 2.70% 3.00%
      106% 2.30% 2.70% 3.60% 4.10% 4.50%

      Based on this setup, the formula is:
      =INDEX($B$2:$F$7,MATCH(86%,$A$2:$A$7,1),MATCH(93%,$B$1:$F$1,1))
      you can replace 86% used in the first match with a cell reference to your target sales, and 93% from the second match with your cell containing your average selling price.
      Catalin

      Reply
      • Faisal

        May 11, 2018 at 12:39 am

        AVG SELLING SLAB
        Sales Target >=100% 90-99 89-80 70-79 <=69
        106% 4.50% 4.1% 3.6% 2.7% 2.3%
        actual table like this

        Reply
        • Catalin Bombea

          May 11, 2018 at 3:02 pm

          Hi Faisal,
          I saw your current structure of your lookup table. If you want a formula to work, you will have to reformat your lookup table, like the one I sent, it’s the most simple structure that will need a simple formula. If you’re stuck with your structure, then the formula will be more complex, you will need helper columns to analyze an entry. Not recommended.

          Reply
  208. Ian

    May 2, 2018 at 9:51 am

    Hi, I’m hoping you can help me with a little problem that should be simple, i’m just missing it.

    Basically what i want to return is:

    Look up A1 on sheet 1 – Find A1 value on Sheet 2 in column A then return value for Column C in that row IF value from Column B from that row = xyz

    ie, if i have a reference number on Sheet 1, i want to find that reference number on Sheet 2, and pull back a date value from Sheet 2 if the status = ‘in progress’.

    Let me know if thats not clear?

    Reply
    • Catalin Bombea

      May 2, 2018 at 7:31 pm

      Hi Ian,
      If you don’t want an array formula with multiple conditions, the easiest way is to set in column D a simple formula to merge columns A and B values, then your formula should look into column D:
      =Index(Sheet2!C1:C100,Match(A1 & “in progress”,Sheet2!D1:D100,0))
      If you want to use an array formula:
      =Index(Sheet2!C1:C100,Match(A1 & “in progress”,Sheet2!A1:A100 & Sheet2!B1:B100,0)) (confirmed with CSE).

      Reply
      • Ian

        May 4, 2018 at 5:32 am

        Thanks Catalin!! This worked out great (i used the Array formula).

        Now, lets say i wanted to use an if statement, where if the value returned was an ‘#N/A’, then it would do a different function (A3-A5) for example.

        So, IF (the formula you wrote) returns a number, leave the number, else perform (A3-A5)

        Is that possible?

        Reply
        • Catalin Bombea

          May 5, 2018 at 3:51 am

          It is possible, but you have to write the formula twice:

          =IF(ISNUMBER(formula here), formula again here, A3-A5)
          Array formula, of course. I would go for helper columns, if you have lots of formulas, it will be faster than array formulas.
          Cheers,
          Catalin

          Reply
          • Ian

            June 22, 2018 at 7:01 am

            Hi Catalin,

            I’m hoping i can call on your super expert help again for one more issue I am running into.

            Everything we discussed prior is working as a dream. what i found is that on Sheet 2, there are multiple conditions that match (didn’t expect that initially) and it is only pulling back the first record in the sheet that it matches.

            Here is what i’m using:

            =INDEX(‘Sheet2’!$H$2:$H$100,MATCH(‘Sheet1′!B4 & “In Progress”,’Sheet2′!$A$2:$A$100 &’Sheet2’!$G$2:$G$100,0))

            So this is basically a reference number on Sheet 1, that matches to a reference number on sheet 2, then when it MATCHES the reference number on sheet 2 it looks to match a second field “In Progress”, and if those two match returns a value from a 3rd cell.

            What i’m running into is there are multiple rows that meet a condition where the reference number matches and it matches the second field “in Progress”. I need to have all the rows that meet this condition add the 3rd field together (the 3rd field is a numeric / time value)

            Example:

            match Reference number 001 in column A on sheet 1 to a reference number on sheet 2 in column A. then check that match to column B to see if column B for that same row has the text “in Progress”. then for each row that matches those two conditions, Add all the numbers from Column C and return the added number value.

            hope i explained that so it makes sense? Any help you can provide would be super appreciated again!!

          • Catalin Bombea

            June 25, 2018 at 12:55 pm

            Hi Ian,
            It’s not an Index-Match any more, use this to sum all matches:
            =SUMPRODUCT(Sheet2!$C$2:$C$100*(Sheet2!$A$2:$A$100=Sheet1!B4)*(Sheet2!$G$2:$G$100= “In Progress”))
            Cheers,
            Catalin

  209. April

    May 1, 2018 at 8:13 am

    Hello,

    I think what I am trying to do is relatively simple but I cannot get the formula just right or to apply to the whole spreadsheet. I need to take Column B of dates and have them automatically highlight red if they are more than 14 days from the dates in Column A.

    For example I found that this formula works for each individual set of dates but I don’t want to have to type it in for each and every column.

    =$B2>$A2+14 in the conditional formatting tools this works and but it’s not working for the whole table. I need A1 to coordinate with B1 and A2 to coordinate with B2 etc.

    Anyone know how to do this? I know I am so close and it is driving me insane

    Reply
    • Mynda Treacy

      May 1, 2018 at 10:02 am

      Hi April,

      Your formula looks correct. You need to select the whole range of cells you want formatted before applying the Conditional Format. I explain how Conditional Formatting formulas work in detail here: https://www.myonlinetraininghub.com/excel-conditional-formatting-with-formulas

      If you’re still stuck please post your question in our Excel Forum where you can upload a sample Excel file.

      Mynda

      Reply
  210. Alex

    April 22, 2018 at 1:56 pm

    Hi I have a problem that basically says that if B7=0 and B8>20000 then it’s a ‘yes’. If it’s false then I have to insert a nested if function (using AND) to determine if B7>0 and B8>200000 (when which case it’s a ‘no’ if false)

    Basically it’s asking if the goal of 20000 was met if there were no fundraisers for the months of Jan, Feb, and April. But if there were fundraisers in the months of March and May then the goal of 200000 had to be met.

    I’m just very lost and would love some help!

    Reply
    • Catalin Bombea

      April 25, 2018 at 11:51 pm

      Hi Alex,
      Try:
      =IF(AND(B7=0,B8>20000),”Yes”,IF(AND(B7>0,B8>200000),”>200000″,”No Condition met”))
      Cheers,
      Catalin

      Reply
  211. Wil

    April 18, 2018 at 3:19 pm

    COLUMN A is a list of names, ROW 1 is a list of dates, intersecting cells have a value such as X: Can I write a formula to search by row 1 for the coordinating information from column A if the X value is present? For example, If A2 contains “Billy Bob”, A3 contains “Phil”, A4 contains “Charles” and B1 contains “April 3”, and B2, B3, B4 (the intersecting coordinates) has “X”, I want to be able to search by the B1 value with the result of all corresponding A values. So search: April 3, Result: Billy Bob, Phil, Charles. ANY HELP IS GREATLY APPRECIATED!

    Reply
    • Mynda Treacy

      April 18, 2018 at 9:50 pm

      Hi Wil,

      I’d use a PivotTable. Put the names in the row labels and drag the date you want the list for into the values area. This will count the Xs against each name, then you can filter out any zero results.

      If you get stuck please post your question in our Excel forum where you can upload a sample Excel file that we can put a solution into.

      Mynda

      Reply
      • Wil

        April 19, 2018 at 4:07 am

        THANK YOU SO MUCH! I will try that!

        Reply
  212. Rachel

    April 5, 2018 at 1:41 am

    Hi,

    I am doing a leave tracker to record all the employees in a year.

    There are 4 categories of staff with different leave entitled as well years of services.

    Group M
    2years is 18days, >5years is 22days

    Group E, Group A
    2years is 16days, >5years is 20days

    Group C
    2years is 14days, >5years is 18days

    I need the excel will automatically define the correct leave entitled with group data

    Is there any good formula to work up?
    Seek for help, thank you.

    Reply
    • Catalin Bombea

      April 7, 2018 at 5:24 am

      Hi Rachel,
      Try this formula:
      =INDEX(CHOOSE(INDEX({1,2,3},MATCH(C1,{0,2,5},1)),{0,0,0,0},{14,16,16,18},{18,18,20,22}),MATCH(D1,{“C”,”E”,”A”,”M”},0))
      In cell C1, it should be a number representing the number of years, you can use a formula like this: =DATEDIF(A1,B1,”y”)
      In D1, you should have the group name: C, E, A or M.
      Cheers,
      Catalin

      Reply
  213. jo

    April 4, 2018 at 8:43 am

    I need to have 5 emails sent from a worksheet with more than 50 attachments. The workbook would have 77 worksheet with this requirement.

    And I want to share this with other people so that they can use it without any need to edit the code to personalize for them.

    Reply
    • Mynda Treacy

      April 4, 2018 at 9:15 am

      What’s your question, Jo? Perhaps this tutorial will help: Create a PDF from Excel and email it with Outlook.

      Reply
  214. SPR

    April 2, 2018 at 2:49 am

    Hi,

    Anyone can help me on this.

    Current formula i am having now is below.

    =ROUND(IF(AND(B6=”E”,C6=”BTT”),SUM(G6,I6)*5%,SUM(G6,I6)*3%),0)

    Now i have a new condition. ie
    A B C D E F G (E*F) H I(E*H) J
    O 2 6000 3 18000 5 30000 480
    O 1 3000 1 3000 2 6000 110

    in “J” i need SUM of (1% OF “G” OR 50*”D” WHICHEVER IS HIGHER)+(1% OF “I” OR 50*D WHICHEVER IS HIGHER)

    I need this criteria to be included in the above formula.

    Reply
    • Catalin Bombea

      April 3, 2018 at 3:40 pm

      Hi SPR,
      Can you please uplload a sample file with your sample data and a manual result for that sample data? Use our forum to upload. (create a new topic after sign-up)
      Catalin

      Reply
  215. Vinay Chavan

    April 2, 2018 at 2:20 am

    Merit List Decide the Eligibility of Student for the admission
    Direct admission: if Total Marks is >600 & Sum of Maths Marks & science Mark is >=180
    1st Merit List: If Total Marks is >=500 & Maths Marks >=55 & Science Mark >=55
    2nd Merit List: If Total Marks is >=400 & Maths Marks >=55 & Science Mark >=55
    3rd Merit List: If Total Marks is >=300 & Maths Marks >=50 & Science Mark >=45
    Else not Eligible
    Kindly solve the problems

    Reply
    • Catalin Bombea

      April 3, 2018 at 3:38 pm

      Hi Vinay,
      Try this formula:

      =CHOOSE(IF(AND(A2>600,SUM(B2:C2)>=180),1,IF(AND(A2>=500,B2>=55,C2>=55),2,IF(AND(A2>=400,B2>=55,C2>=55),3,IF(AND(A2>=300,B2>=50,C2>=45),4,5)))),"Direct admision","1st Merit List","2nd Merit List","3rd Merit List","Lazy")

      in column A: total marks, column B: Math marks, column C: Science marks.

      Reply
  216. ahsan

    March 31, 2018 at 7:30 am

    sum, count, average or any other formula not working on this below mention data. Furmulas worked when we rearrange cell with the help of F2 function key or double click on cell. Have any other option to rearange complete sheet or worksheet imediately?

    Reply
    • Mynda Treacy

      March 31, 2018 at 8:01 am

      Hi Ashan,

      I’m not sure what you mean, but maybe you have calculation set to manual? Check the Formula tab > Calculation options.

      Mynda

      Reply
  217. maro

    March 30, 2018 at 8:32 pm

    IF both A1+B1 7 but 9 but 20, return $200

    I can not write it in Excel

    Reply
    • Mynda Treacy

      March 30, 2018 at 9:37 pm

      Hi Maro,

      I’m not sure what you mean. Are you saying if A1+B1= 7, and are less than 9, then 20….???

      Perhaps you can post your question and a sample Excel file on our forum we’ll be able to see what you’re trying to do.

      Mynda

      Reply
  218. Uzma Razzaq

    March 29, 2018 at 7:33 pm

    Some assistance needed please..
    i need a formula for excel sheet. As i have record of fee of school students.
    Condition: If more than one child of a parent studying in same school they will be granted fee compensation as

    100% fee for first child
    75% fee for Second child
    50 % for all others & so on

    if one child left the school, 100% will replaced by 75% &
    75% will be replaced by 50%

    what formula can i use for automatic replacement, pleas help me in this regard

    Reply
    • Mynda Treacy

      March 30, 2018 at 8:53 am

      Hi Uzma,

      It’s difficult to say without seeing the layout of your Excel file. e.g. do you have rows containing the names of each student and a corresponding column that has the count of family members for that student? If so it would be relatively easy, but if you don’t have that count then you need to calculate that first. Let’s assume you have the count of family members in column B beside each student listed in column A:

      =IF(B1=0,100%,IF(B1=1,75%,50%))

      If you still have questions please post them in our Excel forum where you can also share the Excel sample file.

      Mynda

      Reply
  219. Devon Chipman

    March 23, 2018 at 7:08 am

    Hello,

    I have a formula that is giving me a headache. I am trying to do this:
    Example:If cell B2=1 then cell C2 equals .043 or if cell B2=2 then cell C2 equals .087.

    I am trying to type a number in B2 (1,2,3,4) and then have C2 put in the number that it is tied to or equal to.

    Reply
    • Catalin Bombea

      March 24, 2018 at 2:45 am

      Hi Devon,
      Try this formula in cell C2 please:
      =IF(B2=1,0.043, IF(B2=2,0.087,”Other value in B2″))
      Cheers,
      Catalin

      Reply
  220. selvaraj

    March 21, 2018 at 8:34 pm

    Landing Date & Time Delivery Taken Date & Time Del-Lan Result
    3-4-18 22:00 3-6-18 12:01 38:01:00 Greater 24 Hours
    3-5-18 10:30 3-6-18 12:01 25:31:00 Greater 24 Hours
    3-7-18 4:14 3-7-18 6:35 2:21:00 LESS4
    3-16-18 9:00 3-16-18 14:25 5:25:00 4 TO 8 HOURS
    3-15-18 22:45 3-16-18 7:15 8:30:00 8 TO 12 HOURS

    i did formula like this, but answer is not getting for greater 24 hours,
    =IF(AI38<TIME(4,0,0),"LESS4",IF(AI38<TIME(8,0,0)," 4 TO 8 HOURS",IF(AI38<TIME(12,0,0)," 8 TO 12 HOURS",IF(AI38<TIME(24,0,0)," 12 TO 24 HOURS"))))

    pl help me to share formula

    Reply
    • Catalin Bombea

      March 24, 2018 at 2:43 am

      Hi selvaraj,
      Date and time problems are very sensitive, there are many factors that can affect the calculation.
      I think it’s best for you to upload a sample file with your sample data, this way we can analyze the formats you have and provide a functional solution.
      Please use our forum to create a new topic and upload the file.
      See you on forum.
      Catalin

      Reply
  221. HABEEB RAHMAN

    March 20, 2018 at 7:08 pm

    i have one doubt

    i have one product 3600 PCS
    last purchase date is 1st Jan 2018 -2300 pcs
    last before 13th November 2017 -2300 pcs

    whats is formula or program to calculate this ageing in excel

    Reply
    • Catalin Bombea

      March 21, 2018 at 3:20 pm

      Hi Habeeb,
      Not sure what you want to achieve. Are you trying to calculate the difference between nov 2017 and jan 2018? This should be a simple deduction between those 2 cells.
      Can you upload a ample file to our forum and clarify what you’re after?
      Thanks
      Catalin

      Reply
  222. Steve

    March 20, 2018 at 3:55 am

    Hello,
    i am trying to do this formula
    =IF(B3=1,AND(C3=5,15,PRODUCT(B3:C3)))
    what i want to do is if cell B3 is equal to 1 and cell C3 is equal to 5, then i want it to return 15, Else i want Product of cell B3 and C3
    Is this possible, if so What am i doing wrong?

    Reply
    • Catalin Bombea

      March 20, 2018 at 4:12 am

      Try this:
      =IF(AND(B3=1,C3=15),15,B3*C3)

      Reply
      • Steve

        March 20, 2018 at 4:52 am

        That is Awesome, but i have 1 more condition to add in:

        =IF(AND(B3=1,C3=5),15,OR(IF(B3=2,C3=5),15,B3*C3))
        same thing, but i need to get the same result 15, or product if B3=2
        am i doing too many steps?
        if B3 is either 1 or 2 and C3 is 5, i need the result to be 15, or the product of the 2 cells

        Reply
        • Catalin Bombea

          March 20, 2018 at 3:26 pm

          Then it should be:
          =IF(AND(OR(B3=1,B3=2),C3=5),15,B3*C3)
          There are many ways to write this, here is another version:
          =IF(OR(B3*C3=5,B3*C3=10),15,B3*C3)
          In this case, it does not matter which cell has the 1 or 2 and which has the 5, so you should know better which one fits to your needs.

          Reply
          • Steve

            March 20, 2018 at 8:36 pm

            You are Awesome!!!!, have a great day

          • Catalin Bombea

            March 21, 2018 at 3:18 pm

            You too 🙂
            Cheers,
            Catalin

  223. Josh

    March 15, 2018 at 2:48 am

    Hi,
    I’m trying to make a spreadsheet that can automatically assign a time slot to candidates that are successful in a test. For instance, Pass Test A then receive time slot. However, the difficulty comes in doing this for multiple candidates. For instance when the next person is entered as Pass Test A they would receive a timeslot 10 mins after the previous candidate. Is this something that can be automated?

    Reply
    • Catalin Bombea

      March 16, 2018 at 3:33 am

      Hi Josh,
      There is always a way, sometimes not exactly as your expectations, there may be design changes if something is not possible.
      Can you upload on our forum a sample file so we can see your structure? (open a new topic after sign-in). A theoretical answer might not be what you need, we can try to find a personalized solution for you. Don’t forget to add as many detail as possible to clarify things, a fast response usually means that you did a good job explaining what you need 🙂
      Regards,
      Catalin

      Reply
  224. Pat

    March 15, 2018 at 1:19 am

    I need assistance. In the below chart if cells in column “A” equal 4656 AND the cell in column “G” equals 50, then I need to get the sum of only the cells in column F that correspond. I need it to look at the whole column of each.
    A B C D E F G
    1 8623 RHODES AARON 8623012287 3/1/2018 25.54 50
    2 8623 HERRIN AARON 8623012288 3/1/2018 84.86 50
    3 8623 JACKSON DYLAN 8623012286 3/1/2018 10.34 50
    4 8623 RHODES DYLAN 8623012287 3/1/2018 47.88 60
    5 8623 RHODES FRANK 8623012287 3/1/2018 31.92 80
    6 4653 HEAD GLENDA 4653009783 3/2/2018 115.34 50
    7 4653 HEAD GLENDA 4653009783 3/2/2018 154.81 60
    8 4656 JOHNS GLENDA 4656017111 3/2/2018 388.59 50
    9 4656 HART JADE 4656017109 3/2/2018 57.29 50
    10 4656 JOHNS JERRY 4656017111 3/2/2018 477.12 80
    11 8623 SIMMONS LATISHA 8623012293 3/2/2018 61.15 50
    12 8623 MITCHELL LATONYA 8623012281 3/2/2018 28.8 50
    13 8623 HARPER LATONYA 8623012292 3/2/2018 9.83 50
    14 8623 SIMMONS PENELOPE 8623012293 3/2/2018 65.52 60

    Reply
    • Catalin Bombea

      March 16, 2018 at 3:29 am

      Hi Pat,
      You can try this formula:
      =SUMPRODUCT((A1:A14=4656)*(G1:G14=50)*(F1:F14))

      Reply
  225. Johnson

    March 14, 2018 at 5:02 am

    I am putting together an inventory spreadsheet. I need 1 case to equal 24 units. (I’m inputting cases of beer). What formula do I use?

    Reply
    • Mynda Treacy

      March 14, 2018 at 9:59 am

      Hi Johnson,

      I suspect there are some details missing from your description, because at its most basic the formula is:

      =1*24

      If that isn’t what you were after please post your question and sample Excel file on our Excel Forum where we can better understand what you need.

      Mynda

      Reply
  226. DJ

    March 2, 2018 at 8:00 am

    what is the formula to calculate to see if 3% is lesser than 30%

    Reply
    • Catalin Bombea

      March 2, 2018 at 1:33 pm

      Hi DJ,
      Not sure what you mean, 3% will always be smaller than 30%. Can you give us more details?
      Catalin

      Reply
  227. Kipp Play

    February 28, 2018 at 2:30 am

    =IF(C5>C3,”local”,IF(AND(C5>=5534,C5C3=local. Then I need two more answers, if C5>E3 = okay, but if C5<=E2 =More.

    Reply
    • Catalin Bombea

      February 28, 2018 at 3:04 am

      Hi Kipp,
      Try this:
      =IF(C5>C3,”local”, IF(AND(C5>=5534,C5<>C3),”local”,IF(C5>E3,”okay”, IF(C5<=E2,"more","other")))) Nested ifs require a specific order of conditions, when the first true condition is met, the rest will not be evaluated.

      Reply
  228. Kipp Play

    February 28, 2018 at 2:23 am

    =IF(C5>C3,”local”,IF(AND(C5>=5534,C5C3, then place local in the filed. And if C5 is >=E3, then place Okay in the field, but if C5 is <= E2 then place Needs more in the filed.

    I just can't seem to get the formula to work the way that I need it.

    Reply
  229. mohasin

    February 27, 2018 at 11:51 pm

    in o26 value is 5000 and next cell o27 value is fix which is 10000 now i know that if cell o26 value is less than 10000 came cell o26 value but cell o26 value is more than 10000 than came only fix value 100000 plz tell formula

    Reply
    • Catalin Bombea

      February 28, 2018 at 2:54 am

      Hi ,
      Please use our forum to upload a sample file, your request is not clear, hopefully a sample file will clarify the request. Create a new topic after you sign-in to upload the file.
      Regards,
      Catalin

      Reply
  230. Subash

    February 10, 2018 at 10:48 pm

    Hi Mynda,

    Thanks for all the pains you and your team take to respond to queries of people visiting your site.

    Regarding IF AND function, I am having a problem, kindly assist.

    I have Nikhil in A1

    in D1 I have put this formula

    =IF(AND(A1=”Nikhil”,B1=0),”good”,IF(AND(A1=”Nikhil”,C1=4),” very good”))

    even though I do not enter 0 in B1 I am still getting the result “good” in D1 whilst as I should get “very good” as I have put 4 in C1

    I know excel treats 0 as some value, which I cannot remember.

    Kindly assist as I have a list of 2600 rows to work with. -:)

    Best regards,
    Subash

    Reply
    • Catalin Bombea

      February 11, 2018 at 2:43 am

      Hi Subash,
      If B1 is empty, excel will consider it 0, so you need another check:
      =IF(AND(A1=”Nikhil”,B1=0, Len(B1)>0),”good”,IF(AND(A1=”Nikhil”,C1=4),” very good”))
      Or, put first the other condition, there is a specific behavior with nested IF statements: if a condition is met, the rest of IF statements will not be evaluated.
      =IF(AND(A1=”Nikhil”,C1=4),” very good”),IF(AND(A1=”Nikhil”,B1=0),”good”,”other case”))

      Reply
      • Subash

        February 12, 2018 at 3:48 pm

        Thanks Mynda,

        You guys are the greatest.

        When I added the LEN function it worked. But it doesn’t work when I shifted the conditions as you suggested.

        Thanks and best regards,
        Subash

        Reply
        • Catalin Bombea

          February 12, 2018 at 5:13 pm

          You’re welcome, glad to hear you managed to make it work.
          Catalin

          Reply
  231. Judy

    February 6, 2018 at 6:35 am

    I need help in getting a formula to help with the following. In my spreadsheet I have Hours earned for going to monthly training in cells D2 through O2. I also have the number 1 for each member if they attended the monthly meeting. I want to add the numbers in Cells D2 thought O2 ONLY if there is the number 1 in cell D5 through O5. An so on for the 75 members in the group. I want to put the answer in cells Q for each member.

    I tried =SUMIF(D2,O2,D5,O5,1)

    and I tried SUMIF(D2,O2,D5,O5,”1″)

    Reply
    • Catalin Bombea

      February 6, 2018 at 2:26 pm

      Hi Judy,
      Can you please upload a sample file on our forum? (create a new topic)
      It will be easier to provide a functional formula for your specific data structure.Thanks
      Catalin

      Reply
  232. Carel van der Merwe

    January 10, 2018 at 4:11 pm

    Good day,

    I need your assistance please. I have two columns J & K which my information are in. “07/06/2017 05:06 PM” & “11/01/2017 02:08 PM” you can see the information in each cell contains the date as well as the time.. What i want to achieve is a true or false statement based on the time in each cell to calculate whether the difference between the two times are less than four hours of more than four hours difference. Meaning if less than 4 hours difference = true, more than 4 hours difference = false.
    I hope and trust that my explanation makes sense. THANK YOU IN ADVANCE!

    Reply
    • Catalin Bombea

      January 10, 2018 at 7:04 pm

      Hi Carel,
      If you want to ignore the dates and work only with times, you can try this:
      =ABS(A1-INT(A1)-(B1-INT(B1)))>4/24
      A time value will always be less than 1, as a day is an integer, one hour will be 1/24.
      A1-INT(A1) will extract only the time fraction, ignoring the days, so it’s just a matter of deducting the time fractions and compare them with 4/24 (4 hours, converted to decimal system).
      Catalin

      Reply
      • Carel van der Merwe

        January 10, 2018 at 7:31 pm

        Hi Catalin,

        Thank you very much for you very much for your prompt response. I would like to take the dates into consideration as well seeing the i need to calculate the 4 hour difference =true/false? Is that possible?
        So i basically need the calculation to be done on a calendar basis including time running on a 24 hour time format (am & pm) (“,)
        Hiya did you get my response?

        Reply
        • Catalin Bombea

          January 11, 2018 at 12:12 am

          Hi Carel,
          Then simply deduct those 2 cells with dates:
          =B1-A1>4/24

          Reply
          • Carel van der Merwe

            January 11, 2018 at 4:25 pm

            You’re a star! Thank you!

          • Catalin Bombea

            January 11, 2018 at 7:23 pm

            You’re welcome 🙂

  233. Sachin Vahile

    January 9, 2018 at 7:16 pm

    IF THE GIVEN VALUE IN A CELL IS MORE THAN 3500 THEN DEDUCT 12 OR DEDUCT 6 FOR ANY VALUE

    Reply
    • Catalin Bombea

      January 9, 2018 at 9:42 pm

      Hi Sachin,
      Try this:
      =A1-IF(A1>3500,12,6)

      Reply
  234. Khan Fakhar

    January 9, 2018 at 3:39 pm

    Dear i need help with the following formula , as i am making an estimation sheet for a paint company. for example a paint bucket is sufficient for 80 meter, if we take a job of 50 meter we have to use the same bucket and there will be wastage of 30 meter paint so we have to charge it to client. what i want is
    if the value in the cell =if(i3 is greater then 0.1 but less then or equal to 1 then the value in cell E3 =1 ,but the i3 is greater then then 1 then its actual value to shown in E3 )
    hope you can help me with this as i have be trying to figure it out since 3 days but i couldn’t make it.
    regards

    Reply
    • Catalin Bombea

      January 9, 2018 at 9:45 pm

      Hi Khan,
      Try this formula:
      =IF(AND(I3>0.1,I3<=1),1,IF(I3>1,I3,I3))

      Reply
  235. Sahed

    January 7, 2018 at 9:38 pm

    how to use in excel formula I’ll get 2,000 taka for every 3,000 taka

    Reply
    • Sahed

      January 7, 2018 at 9:43 pm

      i ll using this formula 12 column how can i use ?

      Reply
      • Catalin Bombea

        January 8, 2018 at 11:22 pm

        Depends on what you need to do. What do you have in those 12 columns? AND conditions, or IF conditions?
        Please prepare a sample file with a manual result and detailed explanations, we can help only if the problem is clear. Use our forum to upload a file, create a new topic after signing-up.
        Catalin

        Reply
    • Catalin Bombea

      January 8, 2018 at 11:19 pm

      Hi Sahed,
      Try:
      =ROUNDDOWN(A1/3000,0)*2000

      Reply
  236. Parveen Dhariwal

    December 29, 2017 at 3:27 pm

    I want to put formula in excel like we have some students out of which some only understand english but not able to write and speak.
    some understand and write but not able to speak.
    some are able to read, write and speak all three.
    then we have to categorized if someone is able to speak write and understand then he comes under excellent category.
    If he understand and write comes under good category.
    if he understand only then comes under average category.
    if he is not able to speak write or understand then comes under poor category.
    Kindly help in categorizing data as per requirement.

    Reply
    • Catalin Bombea

      December 29, 2017 at 8:36 pm

      Do you have 3 categories or 4? You mentioned Read, Write, Speak and Understand.
      Each student should have 3 columns (or 4, if you have all those 4 attributes mentioned above), with values of 1 and 0. If a simple sum from those 3 columns returns the result 3, it will be “Excellent”, 2 will be “Good”, 1=”Average”, 0=”Poor”.
      You can try a simple formula to return those categories:
      =Index({“Poor”,”Average”,”Good”,”Excellent”},Match(Sum(B2:D2),{0,1,2,3},0))
      This approach will return Good if the student has 1 ability, no matter which one, can be Read, or Write or Speak.
      For more restrictive classification, you can use nested IF’s:
      =If(And(B2=1,C2=0,D2=0),”Average”,If(And(B2=1,C2=1,D2=0),”Good”,”Other Case”)). You can add more cases until you cover all situations.
      I assumed that in column A is the student name, column B is Read, column C is Write, Column D is Speak.

      Reply
  237. Sure

    November 26, 2017 at 7:53 pm

    Hi i want a Coding that Cell J3 should be displayed as True if I3 < I4 to I9 I tried with this coding =If(I3<(I4:I9),"TRUE","") No rsults comes help me to solve this…

    Reply
    • Mynda Treacy

      November 27, 2017 at 8:51 am

      Hi Sure,

      I’ll assume that I3 must be less than all of the values in the range I4:I9. If so, you can use this formula:

      =I3
      

      Mynda

      Reply
  238. Jean

    October 24, 2017 at 2:03 pm

    Hi, i having situation as below. can the formula in 1 cell with below 2 IF situation.
    in fty date later than produce date, can cell show late how many days, no late than show ok
    if in fty date later than produce date <5 days and the production line start with K, can the cell show late how many days – ok

    Reply
    • Mynda Treacy

      October 24, 2017 at 5:18 pm

      Hi Jean,

      Sorry, it’s a bit difficult to follow your example. Can you please post your question on our Excel forum and upload an example Excel file so we can see your question in context.

      Thanks,

      Mynda

      Reply
  239. Melinda Koite

    October 24, 2017 at 8:04 am

    HI! I think I need to use the IF function but please tell me if I’m wrong.

    I’m making a spreadsheet for storage units. And I have columns with the months. I want to be able to put an x under the column month and it equal the amount of the storage unit rent in another column.

    Reply
    • Mynda Treacy

      October 24, 2017 at 9:44 am

      Hi Melinda,

      Possibly, but it’s difficult to say without seeing an example Excel file. Are you able to post your question on our Excel Forum where you can upload an example file so we can help you with a specific solution?

      Mynda

      Reply
  240. lamees

    October 22, 2017 at 7:27 pm

    If the amount is > 750, give a 10% discount.

    Reply
    • Mynda Treacy

      October 22, 2017 at 7:32 pm

      Hi Lamees,

      Try this, where A1 contains your ‘amount’:

      =IF(A1>750,A1*.9,A1)

      In English it reads; IF the amount in A1 is greater than 750, then take 10% off A1, otherwise the amount in A1.

      Mynda

      Reply
  241. G.Harbs

    October 17, 2017 at 11:37 pm

    I need some help on a statement. I recently made several changes to the way we track our estimating and I need to now track every project we bid as a count. Every week, we could bid on over 30 projects ranging from $20,000.00 to $2,000,000.00. I need to figure out a simple formula to track our bids as a count. In other words, I need to know how many bids we are submitting that are $0-100K, 100k-250k, 250k-500k, 500k-1M, $1M-$2M, and >$2M. Thank you for the help!

    Reply
    • Mynda Treacy

      October 18, 2017 at 3:06 pm

      Hi Gus,

      I’d use a PivotTable for this. If you need pointers please post your question and some sample data in an Excel file on our forum and I’ll show you what I mean.

      Mynda

      Reply
  242. Paul Smith

    October 11, 2017 at 8:15 am

    Excellent!! Very helpful.

    Reply
  243. Allen

    October 10, 2017 at 5:09 am

    All conditions are met in And If statement which should give True response, but it gives the False response.

    ‘=IF(AND($D66=”STPC”,$D66=”D-STPC”,I66>=10000),$I66/10000*3,5)

    The STPC represents product type in column D, column I represents qty produced. If more than 10,000 are produced, since both logical conditions have been satisfied, should choose true, but it defaults everytime to answer 5.

    Example: Row 71 = product type of STPC, and has a qty of 187000 in Column I which should give an answer of 18.70 * 3 = 56.10. But it defaults to 5.

    My other And If statements work in other columns, but it is not working in column AN.

    Reply
    • Mynda Treacy

      October 10, 2017 at 10:06 am

      Hi Allen,

      There will be some anomaly in your data that’s causing the FALSE result. I’d have to see it to tell you why. Can you share the your question and file on our Excel Forum?

      Mynda

      Reply
  244. samy

    September 24, 2017 at 1:55 pm

    Thank you so much for the above explanation, however i have one scenario where i need help.

    Excel Column A1. Excel Column B1
    Price Highest price/ lowest price
    1 NO
    2
    3
    4
    5 YES.
    what i need is to populate yes & NO automatically wherever the price is high or low.
    please help me with the formula on this.

    Reply
    • Mynda Treacy

      September 24, 2017 at 7:24 pm

      Hi Samy,

      Assuming your values are in cells A2:A6, you can use this formula:

      =IF(A2=MAX($A$2:$A$6),"Yes",IF(A2=MIN($A$2:$A$6),"No",""))

      Mynda

      Reply
  245. Gk

    September 24, 2017 at 4:14 am

    Hello

    If a person to get based on allocation,his billing type and he worked in full month so what function and what can I put the formula in excel…

    Alloc:- allocation (based on )
    B:- Billable
    Nb:- non billable

    Eg.
    Name Alloc B/ NB start date enddate
    ABC 100% B. 1/1/2017, 31/1/17
    Plz help me
    Awaiting ur reply

    Thank you.

    Reply
    • Mynda Treacy

      September 24, 2017 at 12:11 pm

      Hi Gk,

      Can you please post your question and a sample Excel file on our forum as it’s a bit difficult to understand from your description alone.

      Thanks,

      Mynda

      Reply
  246. RAJESH JOSHI

    September 18, 2017 at 6:24 pm

    CELL VALUE IN PERCENTAGE A1 IS 50.00 SET CRITERIA IS BELOW MENTION
    75-“3.00”

    PLEASE URGENT REPLY …..THANKING YOU

    Reply
    • RAJESH JOSHI

      September 18, 2017 at 6:27 pm

      A1-50.00

      A1 75 CELL VALUE IS “3.00”

      Reply
      • Catalin Bombea

        September 19, 2017 at 3:38 am

        Hi Rajesh,
        Assuming that A1 is a percentage, you should be able to use a very simple formula: =IF(A1=0.75,3,”This will be displayed for any values other than 0.75 in A1″)
        Catalin

        Reply
  247. Jim Baker

    September 12, 2017 at 10:21 am

    I NEED TO WRITE A FORMULA THAT IS SIMPLE BUT FOR SOME REASON I CANT GET IT RIGHT. scenario. cell C1 has total hours worked. I want cell A1 to tell me that if C1 is greater than or equal to 40 than put 40 in A1. I also want A1 to tell me if C1 is less than 40 than put the number in C1 in cell A1

    Reply
    • Mynda Treacy

      September 12, 2017 at 1:43 pm

      Hi Jim,

      Please post your question on our Excel Forum and include your example Excel workbook. Time calculations can be tricky so we need to see what format you’re entering hours etc. in order to help you.

      Mynda

      Reply
  248. Sharlene Harding

    September 4, 2017 at 9:57 pm

    Hi, Could I get help to fix this formula please

    =IF(K4,”Y”)COUNTIF(B4:G4,”>0″)

    Basically if a column has a “Y” count the amount of columns if the value is more than 0

    Reply
    • Mynda Treacy

      September 5, 2017 at 11:05 am

      Hi Sharlene,

      It’s a bit more complicated than an IF or even COUNTIF. You want to count data across multiple columns (B:G), as opposed to a single column, which is what COUNTIF is designed to do. You can use SUMPRODUCT like so:

      =SUMPRODUCT((B4:G6>0)*1*(K4:K6="y"))

      Mynda

      Reply
  249. Paddy

    August 31, 2017 at 8:55 pm

    Hi, I have a problem while using IF function.

    See.. Am trying to use if function Column B & C with a formula that if column B contains CLOSED than C should be CURRENT date and the formula is ” =IF(B2=”CLOSED”,TODAY(),””) “.

    It is working correctly, however, while opening the excel sheet in the next day, it is reflecting the current day and it is not reflecting the date which I actually enter CLOSED in column B on the particular date.

    Can someone please help me to fix, what exactly am I doing wrong?

    Thanks,
    Paddy

    Reply
    • Catalin Bombea

      August 31, 2017 at 9:06 pm

      Hi Paddy,
      Each time excel recalculates, TODAY function will return today’s date.
      You have to use a simple macro that will react to column B values, when you type CLOSED, the code will put today’s date in column C.
      Should be like this:

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Cell As Range
      If Not Intersect(Target, Range("B:B")) Is Nothing Then
      Application.EnableEvents = False
      For Each Cell In Target
      If Cell = "CLOSED" Then Cell.Offset(0, 1) = Date
      Next
      Application.EnableEvents = True
      End If
      End Sub

      The code should be placed in that worksheet vb module.

      Reply
  250. Ashok

    August 5, 2017 at 12:44 pm

    i want to check whether a number is in range a1:e1. suppose if that number is in cell c1, i want to find the largest number in range c1:c10. wats the formula in excel

    Reply
    • Mynda Treacy

      August 5, 2017 at 2:14 pm

      Hi Ashok,

      Please post your question and example Excel file on our Excel Forum, as I’m not sure what you mean by your explanation here.

      Mynda

      Reply
  251. MD.JAHANGIR ALAM

    July 28, 2017 at 4:20 pm

    Hi. How I Can Round In Bonus after Using IF(AND) Formula

    Reply
    • Mynda Treacy

      July 28, 2017 at 5:41 pm

      Try wrapping your formula in the ROUND function:

      =ROUND(IF(AND..)),2)

      Will round the result to 2 decimal places, assuming IF returns a number. It’s difficult to be precise without knowing the details of your formula.

      Mynda

      Reply
  252. ishmail dumbuya

    July 27, 2017 at 11:54 pm

    Please i need a formula for this spreadsheet.
    This is the summary of the formula i need.
    discount of $37.9/MT for every pound below 49lbs to 47lbs. And discount of $75.8/MT below 47lbs to 46lbs and rejection for outturn below 46lbs.
    Discount of $5/MT for every nut above 210 to 215, and above 215 to be rejected.
    Discount of $20/MT for every 1% above 10% to 12% above 12% to be rejected. please i need this formula assistance. Thank you.

    Reply
    • Mynda Treacy

      July 28, 2017 at 5:47 pm

      Hi Ishmail,

      Please post your question and a sample Excel file on our Excel forum so we can see your question in context of your data.

      Thanks,

      Mynda

      Reply
  253. jay_excel

    July 23, 2017 at 4:54 pm

    Hi,

    I need a formula to sum the values based on their respective dates and once the due date appears(monthly), it should start over again from that date until another monthly cycle.

    Thanks

    Reply
    • Mynda Treacy

      July 23, 2017 at 4:59 pm

      Hi Jay,

      It sounds like you need SUMIFS

      Mynda

      Reply
  254. Jay

    July 22, 2017 at 4:13 pm

    Hi,

    Please advise a conditional formatting custom formula to highlight last Thursdays of every month in selected cells.

    Reply
    • Catalin Bombea

      July 22, 2017 at 8:43 pm

      Hi Jay,
      Depends on what you have in those date cells.
      In case you have dates, not text, you can try this conditional formatting formula, assuming that data starts from row 2 in column A:
      =AND($A2>=EOMONTH($A2,0)-7,WEEKDAY($A2)=5)

      Reply
      • Jay

        July 23, 2017 at 7:14 am

        Hi Catalin,

        This has been helpful, undoubtedly. I tweaked the function a bit as follows, considering date column is AK and my weekdays start from Monday.

        =AND($AK2>=EOMONTH($AK2,0)-7,WEEKDAY($AK2)=4)

        Although this is perfect in almost all months, there is some error in few months.

        The months ending on Wednesdays’ such as Jan 2018, Feb 2018, Oct 2018, July 2019, Sep 2020, March 2021, etc. shows the first Thursday of the next month i.e. 1st Feb 2018, 1st March 2018, 1 Nov 2018, 1st Aug 2019, 1st Oct 2020, 1st April 2021, etc. respectively, as highlighted.

        L.E.:
        The months, which are ending with Thursday’s, the formula is considering to highlight the last two Thursday’s of those months. Examples of such months are Aug 2017, November 2017, May 2018, January 2019, February 2019, etc.

        Kindly help.

        Kindly advise. Thanks..

        Reply
        • Catalin Bombea

          July 23, 2017 at 9:33 pm

          Hi,
          Simply change the formula from:
          =AND($AK2>=EOMONTH($AK2,0)-7,WEEKDAY($AK2)=4)
          to:
          =AND($AK2>=EOMONTH($AK2,0)-6,WEEKDAY($AK2)=4)

          Reply
          • jay_excel

            July 24, 2017 at 5:46 am

            It worked.. Thank u so much.. Cheers 🙂

  255. Jay

    July 22, 2017 at 4:07 pm

    I need to create a formula where the text of the target cell, changes from “ACHIEVED” to “PENDING” on a basis of due date reoccurrence, every month. The sum reference cell values are obviously taken from different cells.

    Here is what I got so far. My current status is “ACHIEVED”, but need this to start over again as “PENDING” on 15th of every month, irrespective of any other influencing factors.

    =IF(X3>=S3,”ACHIEVED”,”PENDING”)

    Please Advise

    Reply
    • Catalin Bombea

      July 22, 2017 at 8:45 pm

      Hi Jay,
      We have to see some examples, try uploading a sample file on our forum. (create a new topic)
      Catalin

      Reply
  256. LeNor

    July 20, 2017 at 4:05 am

    Hello. I need a formula that adds up currency in one column if the criteria in another column is met. I have tried a few and can’t get it to work with a range.
    A3:A5 (has either a 1 or 2) and C3:C5 (has currency) I need a formula that says if range A3:A5 is 1 then add C3:C5 that coordinates with 1.
    I hope that makes sense.

    Reply
    • Catalin Bombea

      July 20, 2017 at 9:28 pm

      Hi,
      Can you prepare a sample file with an example of how it should be? At this moment, it’s not very clear. Use our forum to create a new topic and upload the file.
      Catalin

      Reply
  257. Robert

    July 12, 2017 at 8:59 pm

    What is the IF function for C2>E2=G2

    Reply
    • Mynda Treacy

      July 12, 2017 at 9:11 pm

      Hi Robert,

      =IF(C2>E2,G2,0)

      Mynda

      Reply
  258. naveen

    July 6, 2017 at 9:06 pm

    Hai,

    If 0 to 1000 value is 23

    please advise some formula

    Reply
    • Catalin Bombea

      July 7, 2017 at 12:42 am

      Hi Naveen,
      use this one:

      =IF(AND(A1>=0,A1<1000),23,0)
      
      Reply
  259. Emma

    July 6, 2017 at 7:23 am

    Hi Guys,

    If I am using the formula: IF(AND(B2=”TRUE”,C2>1),”A”,”B”) where column B’s outputs (True or False) have been calculated with the OR formula (i.e. not hard-typed); I get the wrong output for my IF formula.

    Any ideas how you get the right output when relying on information that has got formulas on it?

    Thanks

    Reply
    • Catalin Bombea

      July 6, 2017 at 4:43 pm

      Hi Emma,
      Try to use B2=TRUE, without wrapping TRUE between double quotes.
      Catalin

      Reply
      • Emma

        July 7, 2017 at 7:40 am

        Brilliant!

        Thanks 🙂

        Reply
  260. Amit

    June 27, 2017 at 7:44 pm

    hi admin,
    Pls help to make a formula for, if in a cell (J6) is less then 1 then in cell(J7) populate 100%, If in cell (J6) is in between 1 to 5 then cell (J7) populate 50% and If in a cell(J6) is greater then 5 then populate 0%.

    Reply
    • Catalin Bombea

      June 27, 2017 at 8:07 pm

      Hi Amit,
      Try this formula:
      =IF(J6<1,100%,IF(J6<5,50%,0%))
      You will have to decide if the limits should be included, use <= instead of <.

      Reply
  261. JJ

    June 26, 2017 at 5:36 pm

    Hi, if I type in cell A1 a number between 0 and 20 and I want it to give in b2 =1 or 21 to 40 = 2, what formula do I use?

    Reply
    • Catalin Bombea

      June 26, 2017 at 10:21 pm

      Hi JJ,
      try:
      =IF(A1<=20,1,IF(A1<=40,2,3)) There can be many solutions, this is just one of them. Catalin

      Reply
      • Dale H.

        July 8, 2017 at 6:42 am

        What is the three for?

        Reply
        • Catalin Bombea

          July 8, 2017 at 2:31 pm

          Hi Dale,
          is for any other case, not covered by the first 2 evaluations.

          Reply
      • Dale H.

        July 8, 2017 at 7:13 am

        In colum E are # 0-15.Im trying to create an equations that says if E is less than 1=40,if greater than 1 but less than 6 =80, if greater than 6 but less than 12 =120, but if greater than 12 =160
        This is what created”IF(E3<1,40,IF(16,80,IF(612,120,160)))” anything less than 1 it gives me 40 which is correct but everything greater than 1 its giving me 80 which is semi true.

        Reply
        • Catalin Bombea

          July 8, 2017 at 2:35 pm

          Instead of nested IF’s, use a more flexible formula:
          =INDEX({40,80,120,160},MATCH(E1,{0,1,6,12},1))
          Catalin

          Reply
  262. Namdeo

    June 26, 2017 at 1:22 am

    I am trying since long to create a formula in excel sheet using IF & AND function. My challenge is, I want Excel to update remarks automatically basis dates updates in different columns in my inventory sheet. However, I am not able to proceed.

    Reply
    • Catalin Bombea

      June 26, 2017 at 3:32 am

      Hi Namdeo,
      You will have to upload a sample file with details on our forum, from your description is not clear what you want.
      Catalin

      Reply
  263. Symon Jere

    June 23, 2017 at 4:30 pm

    How do I represent the following PAYE info. in an excel:

    First 20000 is free (0%), the next 5000 its 15% and the excess is 30%.

    E.g

    Salary: 40, 000

    First 20,000 free

    Remaining 20,000

    5,000 is 15% = 750

    Remaining (excess) 15,000

    15,000 is 30% = 4500

    Reply
    • Catalin Bombea

      June 23, 2017 at 10:09 pm

      Hi Symon,
      Try this one:

      =IF(D1<=20000,0,IF(D1>25000,750+(D1-25000)*0.3,(D1-20000)*0.15))
      
      Reply
  264. Natasha

    June 20, 2017 at 8:51 pm

    Hi,

    I am trying to calculate depreciated values of assets based on number of years I have owned them.
    the first problem is I am unable to do it all in one cell. moreover, when I split it up, almost all cells were returning values although i have limited my logic to one year. see below:

    Price No of yrs depreciated value > 5 yrs 4-5 yrs 3-4 yrs 2-3 yrs 1-2 years
    28,500 4.05 8,550.00 FALSE 8,550.00 14,250.00 19,950.00 25,650.00

    the correct column for this example should be 4-5 year and this is the formula: =IF(5>H4>=4,G4*0.3,0)

    I have created similar formulas for he rest of the columns and as you can see all are returning values although i have stated a value if false.
    =IF(4>H4>=3,G4*0.5,0)
    =IF(3>H4>=2,G4*0.7,0)
    =IF(2>H4>=1,G4*0.9,0)

    Reply
    • Catalin Bombea

      June 21, 2017 at 2:09 pm

      Hi Natasha,
      Use the AND function to combine multiple logical tests:
      =IF(AND(4>H4,H4>=3),G4*0.5,0)

      Reply
  265. Ludi

    June 19, 2017 at 10:36 pm

    Hope someone can help.

    In column C, I have text.
    In column D, I have account numbers. Their format is for example: 505.1605.130
    Another account number will be 505.2450.100

    Now the 16 in the first account number example indicates that it is an asset account.

    Not if the account in column D is an asset account, I want the cell $H$8 displayed in column H and the row that the account is in.

    BUT, in the same formula, I want to look up in column C (which is a text column) for the word Depn between multiple words, and also return $H$8 to the row in column H.

    I’ve started with the following formula, but it still need a lot of work.
    =IF(AND(LEFT($D$12,5)=1,LEFT($D$12,6)=6),$H$8,””)

    Can it be done?

    Reply
    • Catalin Bombea

      June 20, 2017 at 2:03 pm

      Hi Ludi,
      Use MID to find the account:=IF(AND(MID(D8,5,2),ISNUMBER(SEARCH(“depn”,D8)))=”16″,H8,””)
      Catalin

      Reply
  266. rj

    June 19, 2017 at 5:26 pm

    i want formula for (if A1999 than A1*6/100)

    Reply
    • Catalin Bombea

      June 19, 2017 at 6:03 pm

      Try:
      =IF(A1999,A1*6/100,0)
      You will have to specify the condition that should be met: A1999>0, or A1999=”Yes”

      Reply
  267. Kevin Frazier

    June 1, 2017 at 11:51 pm

    I am trying to track sick time, I want it to be that they earn 1 hour for every week worked, max of 80. once they use some it should subtract that and keep tracking example

    week 80 they are at 80
    week 81 they are at 80 still
    week 82 they use 8 hours so they are now at 72
    week 83 they get 1 hour so they are at 73

    can you do this?

    Reply
    • Mynda Treacy

      June 2, 2017 at 9:31 am

      Hi Kevin,

      Sure, but you’re best to upload a sample file to our Excel Forum so we can see your question in context of your data layout and give you a solution.

      Mynda

      Reply
  268. Ashley Rader

    May 31, 2017 at 5:18 pm

    Say I am doing Times in columns A and B and I want to do an IF statement that Returns a Green highlight If the time in column A is earlier than the time in Column B and Returns a Red highlight IF the time in column A is Later than the time in Column B, how do I do this?

    Reply
    • Mynda Treacy

      June 1, 2017 at 10:34 am

      Hi Ashley,

      You need to use Conditional Formatting for this. I cover that here: https://www.myonlinetraininghub.com/excel-conditional-formatting-with-formulas

      If you get stuck please post your question and Excel file on our Excel forum where we can give you a specific answer.

      Mynda

      Reply
  269. Shanu Singh

    May 29, 2017 at 10:14 pm

    Hi,

    I have a problem if somebody can help me it would be very appreciable. I had wrote many formula but did not get results.
    Have data like:
    Age Actual Value Exempted Value
    50 30000
    50 14500
    50 25000
    60 30000
    60 19100
    60 35000
    We want to get results:
    Age Actual Value Exempted Value
    50 30000 25000
    50 14500 14500
    50 25000 25000
    60 30000 30000
    60 19100 19100
    60 35000 30000
    Note: There are upper cap for 60 year old 30000.

    Thanks
    Shanu

    Reply
    • Philip Treacy

      May 30, 2017 at 8:26 am

      Hi,

      You haven’t provided any logic/rules how to calculate that exempted value so I can’t help.

      If you can provide this and a sample workbook, and post a question on the forum, someone might be able to help.

      Regards

      Phil

      Reply
      • Shanu Singh

        May 30, 2017 at 4:19 pm

        Sure Thanks

        Reply
  270. Ahsan

    May 28, 2017 at 7:09 pm

    This is Ahsan. I have created a new logic which is mentioned below.
    =IF(A20.75,A2*20%+A2))
    by this logic any one can set any new target with below mentioned management criteria such as (if existing employee achieve 0 to 0.75 then his new target will be 0.75 but if existing achievement is more then 0.75 then 20% achievement will be add with existing achievement). Hope this logic will help with other.

    Correction comments:
    =IF(A20.75,A2*20%+A2))

    Reply
    • Mynda Treacy

      May 29, 2017 at 8:41 am

      Hi Ashan,

      I think you mean

      =IF(A2<=0.75,0.75,A2*1.2) Mynda

      Reply
  271. Sara

    May 28, 2017 at 3:49 am

    Hello!

    I am trying to create a slightly complex IF/THAN. If H2 = Late and G2 >= 30 then yes, otherwise no; If H2 = Early and G2 >= 15 then yes, otherwise no.

    Column G is supplied via an ABS formula, and Column H are manual entries.

    Thanks!

    Reply
    • Mynda Treacy

      May 28, 2017 at 9:37 am

      Hi Sara,

      Try this:

      =IF(AND(H2="Late",G2>30),"Yes",IF(AND(H2="Early",G2>15),"Yes","No"))

      Mynda

      Reply
      • Stace

        June 15, 2017 at 11:01 am

        Hi there, I am trying to do something similar but want to include multiple if’s in 1 cell to put different results if 2 different cells met certain criteria. How would I combine…I can not seem to get it to work

        If H2= 1 and I2=”15oz” then =”EA” , If H2= 2 and I2=”27ft” then =”CT” ,If H2= 1 and I2=”13cm” then =”CA”

        Appreciate any help
        Thanks!

        Reply
        • Catalin Bombea

          June 15, 2017 at 1:12 pm

          Hi Stace,
          Try:

          =If(AND(H2=1,I2="15oz"),"EA", If(AND(H2=2,I2="27ft"), "CT", If(AND(H2=1,I2="13cm"),"CA","Other case")))

          Catalin

          Reply
  272. Praveen

    May 24, 2017 at 7:07 pm

    If Column A is greater then Column B then Column C should given me “Yes” orelse “No”, if nothing contains in Column A and B then it should given me a blank cell

    Reply
    • Mynda Treacy

      May 24, 2017 at 7:23 pm

      =IF(AND(A1="",B1=""),"",IF(A1>B1,"Yes","No"))
      Reply
  273. Marty Chanbmil

    May 22, 2017 at 12:01 pm

    Hi,

    I am trying to have excel look up a cell in the same workbook – but per identity.
    In this case cell 21 in each separate identity (represents a date/time entry via a macro.
    This is reflected identically in the Summary Sheet in column A per identity =MickyMouse!A21.

    When the new date is action via the macro in MickyMouse work sheet inside this same working book, the Summary sheet no longer reflects the current entry but rather changes the cell entry:
    =MickyMouse!A22. HO do I automate this to be a true reflection, or a MACRO to check the column and make sure all end in !21.

    Cheers Marty

    Reply
    • Mynda Treacy

      May 22, 2017 at 12:04 pm

      Hi Marty,

      Please post your question and Excel file on our Excel forum so we can see your question in context.

      Thanks,

      Mynda

      Reply
  274. Steven

    May 20, 2017 at 9:29 am

    I am trying to create an expression and any help would be GREATLY appreciated! I am creating a worksheet with a little automation and would like to create an expression that outputs this example; If JANE DOE (name in column 1) will be arriving within the next 30 days (arrival date in column 2 listed as (date formatted) 21 January 2018) show “30 days or less” in column 3

    Reply
    • Catalin Bombea

      May 20, 2017 at 4:55 pm

      Try this:
      =IF(Today()-B1<=30, "30 days or less", ">30 days")
      Catalin

      Reply
      • Steven

        May 21, 2017 at 2:18 pm

        Thanks for the reply. Its a good start but it doesn’t argue the date in column b, it just says “30 days or less” regardless. I tested it by changing the date in column b to 31 December 2018 and it returned with “30 days or less”

        Reply
        • Catalin Bombea

          May 22, 2017 at 1:19 pm

          Hi Steven,
          For future dates, higher than today’s date, use B1-TODAY() in the logical test argument. If the date is smaller than today’s date, use TODAY()-B1. If you want this to work in both ways, use ABS(TODAY()-B1)
          Cheers,
          Catalin

          Reply
          • Steven

            May 25, 2017 at 6:35 am

            Catalin,
            Thanks so much that worked. If its not too much to ask, and im only asking because I cant seem to figure it out and you are already familiar with this problem.

            I would like to output in the same argument “31-90 days”, “91-180 days”, “More than 181 days”

            Thanks again for your time!

          • Catalin Bombea

            May 25, 2017 at 2:45 pm

            Hi Steven,
            Use an Index/Match formula with constants, like:
            =INDEX({"Less than 30 days","31-90 days","91-180 days","More than 181 days"},MATCH(B1-TODAY(),{0,30,90,180},1))

  275. chell

    May 13, 2017 at 6:59 pm

    Do you know how to create an excel formula for this: if b1 is x subtract 50% to a2, or if b1 is y subtract 100% to a2, the answer should appear on cell c2?

    Reply
    • Mynda Treacy

      May 13, 2017 at 9:05 pm

      Hi Chell,

      I’m not sure I understand your question. This part is confusing: “if B1 is x subtract 50% to A2”. What do you mean by “x subtract”? Can you please provide an example using numbers so we can follow the logic and provide you with formula.

      Thanks,

      Mynda

      Reply
  276. Ram Mohan

    May 6, 2017 at 7:38 pm

    If a worker worked for 15days, but he will be paid for 10days, due to poor performer. Similar for 50workers. What formula to be used in excel

    Reply
    • Catalin Bombea

      May 7, 2017 at 3:34 am

      Hi Ram,
      Please upload a sample file with your data and a better description of the problem on our forum (create a new topic).
      Catalin

      Reply
  277. Alan

    May 3, 2017 at 11:22 pm

    Trying to figure out a rolling time period of 75 days where the previous day and the data associated with it falls off.

    Reply
    • Catalin Bombea

      May 4, 2017 at 1:26 am

      Hi Alan,
      Try uploading to our forum (create a new topic) a file with sample data and an example of the desired result, it will be easier to understand your situation.
      Catalin

      Reply
      • Alan

        May 4, 2017 at 1:50 am

        Thank you Catalin, I will try that.

        Reply
  278. Alan

    May 3, 2017 at 3:40 am

    Trying to get a formula for dates older than 75 days. If B4>75 days then B4,C4,D4 and E4 will be blank. If B4 is< 75 days then there is no change to any cells. B4 has a date value in it. Any help is appreciated.

    Reply
    • Catalin Bombea

      May 3, 2017 at 9:01 pm

      Hi Alan,
      It’s not possible for a cell to depend on itself and hold different values, depending on it’s own value. Only with vba this can be achieved, not with normal cell formulas.
      Catalin

      Reply
      • Alan

        May 3, 2017 at 11:20 pm

        thank you

        Reply
  279. Madisen

    April 29, 2017 at 5:25 am

    I’ve been struggling to figure out how to type in this IF function for excel. I could really use some help! Thank you!!

    In cell G2, create a formula using the IF function and structured references to create a calculated column that determines the value of Weeks Vacation based on the following criteria:

    a. If the years employed are less than 3, the Weeks Vacation should be 2

    b. If the years employed are 3 or more, the Weeks Vacation should be 3

    I keep getting invalid credentials or the outcomes being all FALSE, TRUE and 0, all the cells being 0, or the first 3 cells being 0 and the last 3 coming out as NULL instead of the correct numbers… The correct answers when the formula is entered correctly is:
    G2:3
    G3:2
    G4:3
    G5:2
    G6:3
    G7:3

    Thanks again!

    Reply
    • Catalin Bombea

      April 29, 2017 at 2:32 pm

      Hi Madisen,
      If you have an employment date column, the formula should be simple:
      =IF(YEAR(Today())-YEAR(EmploymentDate)<3,2,3) (where EmploymentDate refers to that starting date column)
      If you already have the employment years calculated, replace YEAR(Today())-YEAR(EmploymentDate) with your cell reference where you have this value.
      Catalin

      Reply
  280. Philippa Stanworth

    April 29, 2017 at 2:06 am

    I want to put formula in to say if column c says “6W” and column u is greater than 99, add the number of rows with 6W and >99 and divide them by 29 and multiply by 100.
    Help!

    Reply
    • Catalin Bombea

      April 29, 2017 at 2:18 am

      Hi Philippa,
      Try this:
      =SUMPRODUCT((C:C=”6W”)*(U:U>99))/29*100

      Reply
  281. Paul

    April 26, 2017 at 1:15 am

    I need to see if the value in A2 is 10% or higher than the value in B2…should I be using an IF function for this…I can’t figure it out

    Reply
    • Mynda Treacy

      April 26, 2017 at 9:58 am

      Hi Paul,

      You can do this with a simple logic formula like so:

      =A2>=B2*1.1

      If it’s bigger then it will return TRUE, otherwise it will return FALSE

      You didn’t say what you wanted to return if A2 was 10% greater than B2. If you want to return something other than TRUE or FALSE (that will be returned by the formula above) then you’ll need IF.

      Mynda

      Reply
      • Paul

        April 26, 2017 at 11:54 pm

        Thank you Mynda, I think this is a case where I was overthinking..

        Reply
  282. Kristi Gunerius

    April 25, 2017 at 6:07 am

    I have a partial answer to my question. I am trying to get an answer based on a date…
    =IF(TODAY()>=EDATE($C$2,12*10,300,0) AND =IF(TODAY()>=EDATE($C$2,12*15),500,0)

    I am trying to get this in one equation, but I can’t seem to get the two statements joined. They both are working on their own just fine. Basically I am asking that if today’s date is 10 or 15 years from the hire date, then the staff member receives either a 300 or 500 stipend.

    Reply
    • Mynda Treacy

      April 25, 2017 at 8:56 am

      Hi Kristi,

      It looks like your EDATE function isn’t correct. It only has two arguments; Start Date and Months.

      Try this:

      =IF(TODAY()>=EDATE($C$2,12*15),500,IF(TODAY()>=EDATE($C$2,12*10),300,0)

      Mynda

      Reply
  283. Jahayra Gomez

    April 20, 2017 at 8:17 am

    can some one help me with a formula.
    I want to create a formula tat states the following:

    if a3 has a date of xx/xx/xx or grater then “40” but if a3 has a date of less than xx/xx/xx then it should be “0”

    Reply
    • Mynda Treacy

      April 20, 2017 at 9:01 am

      Hi Jahayra,

      Try:

      =IF(A3>=xx/xx/xxxx,40,0)

      Mynda

      Reply
  284. Virendra Sidhu

    April 13, 2017 at 5:39 pm

    I am looking for a formula where if 268 (Column B) was scored out of 284 (Column A), which means it is 88% achieved (Column C), I need a formula that states if Column C is below 60% it is 0 marks, if below 80% then half of the defined mark which is 10 marks of defined 20 marks and above 80% to 100% then actual % of column C of 20 marks and if above 105% then double the marks of above 100% + 100% marks for e.g if 107% then 7% * 2 times for double which comes to 14%, so now 14% of 20 marks which is 2.8 marks + 20 marks = 22.80 marks

    Reply
    • Catalin Bombea

      April 14, 2017 at 4:01 am

      Hi Virendra,
      Try this formula:
      =CHOOSE(INDEX({1,2,3,4,5},MATCH(C1,{0,0.6,0.8,1,1.05},1)), 0, 20*0.5, 20*C1, 20*C1, 20+20*2*(C1-1))
      Your description is very confusing, and there is a range not covered: what should happen if the percentage is between 100 and 105% ? You described only 80-10 and above 105%. The formula for this case, which is value 4 argument of the CHOOSE function, must be changed according to this case needs.
      Catalin

      Reply
  285. Prashant pawar

    April 10, 2017 at 5:30 pm

    i want formula count late mark condition is if employee cross 100 min his deduction 0.5 late make per day if he late.

    Reply
    • Catalin Bombea

      April 11, 2017 at 1:35 pm

      Hi Prashant,
      You will have to upload a sample file to our forum, and provide more information, at this moment it’s not very clear what you want to do, a sample file with your data structure will help clarify the request.
      Thanks for understanding
      Catalin

      Reply
  286. Edouard

    April 6, 2017 at 7:06 am

    I have this formula in the cell X2:
    =If(AND(W2>=$X$10;W2<=$Y$10;C2″”;D2″”;E2″”;F2″”;G2″”;H2″”;I2″”;J2″”;K2″”;L2″”;M2″”;N2″”;O2″”;P2″”;Q2″”;R2″”;S2″”;T2″”;U2″”);”NAF”;”DEL”)
    If these conditions are meet then return NAF otherwise DEL and it works.
    But for me the formula is long, I tried to replace from C2 to U2 with C2:U2″” but is returning #Value!
    Is there anyone who can simplifier this formula for me? Please I need your assistance.

    Reply
    • Edouard

      April 6, 2017 at 7:11 am

      C2 to U2 not blank, the application removed the sign less than and great than after cell numbers.

      Reply
    • Catalin Bombea

      April 7, 2017 at 3:13 am

      Hi Edouard,
      Try this one:
      =IF(AND(W2>=$X$10,W2<=$Y$10,SUMPRODUCT((C2:U2<>"")*1)=COLUMNS(C2:U2)),"NAF","DEL")
      You can replace COLUMNS(C2:U2) with 19, if the number of cells in that range is not going to change.
      You will also have to change the comma separator to your semicolon separator in formula.
      Catalin

      Reply
  287. Jafar Chhapra

    April 5, 2017 at 10:06 am

    You want to add the contents of cells A3 and B3, then add 15%, and display the total in cell C3. What do you type in cell C3 to make this happen?

    Reply
    • Mynda Treacy

      April 5, 2017 at 10:07 am

      Hi Jafar,

      =(A3+B3)*1.15

      Kind regards,

      Mynda

      Reply
  288. Alex Wilkins

    March 29, 2017 at 11:36 am

    trying to add 10 years to a date if another cell = Y and 6 years if cell = N
    would also like to change the color to recognize the N

    a b c d e f
    Y 03/01/2010 03/01/2020
    N 03/01/2010 03/01/2016

    Reply
    • Catalin Bombea

      March 29, 2017 at 8:23 pm

      Hi Alex,
      Try:
      =DATE(YEAR(B1)+IF(A1=”Y”,10,If(A1=”N”,6,0)), MONTH(B1), DAY(B1))
      Catalin

      Reply
    • Mynda Treacy

      March 29, 2017 at 8:27 pm

      Hi Alex,

      I see Catalin has answered your formula question. You can use Conditional Formatting to automatically format the colour.

      Mynda

      Reply
  289. saurabh Verma

    March 27, 2017 at 5:39 pm

    Calculate the division for a student acc. to following conditions:-
    1- If Total marks >=500, then division is 1st.
    2- If Total marks is b/w 400 to 500, then 2nd.
    3- If Total marks is b/w 300 to 400, then 3rd.
    4- If Total marks is <300, then fail.

    How can i do this plzz rpy me…..

    Reply
    • Catalin Bombea

      March 27, 2017 at 9:38 pm

      Hi,
      Try this one:
      =INDEX({0,3,2,1},MATCH(C15,{0,300,400,500},1))
      Catalin

      Reply
  290. Elbeth

    March 16, 2017 at 10:42 pm

    Hi,

    Will you be so kind to assist me.
    I am struggling to get a working formula for what i want to do.

    The 2 cells referred to was pulled through with VLOOKUP or calculated with a nested IF formula,
    =IF(E21=Saturday,F24*1.5,IF(E21=Sunday,F24*2))

    E21: VLOOKUP Formula from another sheet which will indicate whether that day was a Monday, Tues,etc.
    F24: IF nested formula to give a price

    I want the total to automatically calculate the price x1.5 or x2 if it E21 is Saturday or Sunday, and normal pricing should remain if during weekdays.

    However I have received a #NAME? error

    I am still learning via all these helpful posts, will you please assist

    Reply
    • Catalin Bombea

      March 17, 2017 at 4:56 pm

      Hi Elbeth,
      put the text between double quotes:
      =IF(E21=”Saturday”,F24*1.5,IF(E21=”Sunday”,F24*2)), only defined names can be used without double quotes.

      Reply
      • Elbeth

        March 17, 2017 at 6:23 pm

        Thank you for your assistance,

        I have tried this and yet again I receive the NAME error.
        Is it possible that the Saturday and Sunday I refer to in the formula was converted from date directly with format cells dddd function?

        Reply
        • Catalin Bombea

          March 17, 2017 at 8:12 pm

          Are those double quotes copy pasted from the previous reply? If so, you will have to type them from your keyboard, those double quotes copied from a web page might be different than the type of double quotes expected by excel.
          Also, you can create a new topic on our forum, to upload your file, it will be easier to understand what happens there.
          Catalin

          Reply
  291. Jeanine

    March 16, 2017 at 5:48 pm

    Hi,

    I am busy writing a performance review document for my company. In column A will be the job description example “Sales”. Column B you rate the employee as either 1 or 2 = Poor, 3 = Average or 4 or 5 = Excellent.
    I want the description (Poor, Average or Excellent) to come up automatically in column C when you type 1-5 in column B.
    Please assist with the formula to use

    Reply
    • Mynda Treacy

      March 16, 2017 at 9:50 pm

      Hi Jeanine,

      Try this:

      =IF(B1<3,"Poor", IF(B1>3,"Excellent","Average"))

      Mynda

      Reply
      • Jeanine

        March 22, 2017 at 6:56 pm

        Hi Mynda,

        that did work, thank you.
        However, now i want to know, if the sell is blank, it give the if(B1<3, "Poor") rating. however, i want to change it to should the cell be blank, there shouldn't be any rating.

        Please assist
        Thanks
        Jeanine

        Reply
        • Mynda Treacy

          March 22, 2017 at 8:55 pm

          Hi Jeanine,

          =IF(B1="","",IF(B1<3,"Poor", IF(B1>3,"Excellent","Average")))

          Kind regards,

          Mynda

          Reply
  292. priya

    March 1, 2017 at 5:07 pm

    A4=A2-A1, If(A4>30,”YES”,”No”). Does it work or not?

    Reply
    • Catalin Bombea

      March 1, 2017 at 6:08 pm

      Hi Priya,
      If you have this in a single cell: A4=A2-A1, If(A4>30,”YES”,”No”), it will not work.
      If you have in cell A4 the formula: =A2-A1, then in any other cell the formula: =If(A4>30,”YES”,”No”), it will work.
      Catalin

      Reply
  293. Vignesh

    February 28, 2017 at 11:41 pm

    10
    15
    20

    hi friends any one help me to get correct the formula.

    In my excel sheet in row i want to apply this formula.

    if the cell contains 10 the formula cell needs to show as 1
    if the cell contains 11 to 15 formula cell needs to show as 2
    if the cell contains 16 to 20 formula cell needs to show as 3

    Reply
    • Catalin Bombea

      March 1, 2017 at 6:17 pm

      Hi,
      The formula should be:
      =IF(A1=10,1,IF(AND(A1>=11,A1<=15),2,IF(AND(A1>=16,A1<=20),3,0))) It will return 0 for any other alues than the ones you indicated Catalin

      Reply
  294. Michelle

    February 25, 2017 at 4:58 pm

    I wonder if you could help me. I am trying to set up a spreadsheet of products we sell, however some items have GST (Australian tax at 10%) some do not. My C6 column asks if the product has GST and I have a Y or N answer in this field. Further along my sheet in J6 I have a sale price column. Then K6 is GST (=SUM(J6*1.1-J6))
    L6 is sale price plus GST (=SUM(J6+K6))
    How could I write in Excel language… If C6 equals N to leave the K6 as Zero, but if C6 = Y to show the equation I already have? Maybe there is a much easier way and I am complicating things too much!
    I have Googled myself crazy trying to work this out! Hope anyone could help!!

    Reply
    • Mynda Treacy

      February 26, 2017 at 12:33 pm

      Hi Michelle,

      Try this for your GST calculation:

      =IF(C6=”N”, 0, J6*.1)

      If that’s not it please post your question and sample file to our Excel Forum.

      Mynda

      Reply
  295. Marcie Heinz

    February 22, 2017 at 12:13 am

    What is the function written out for this problem?

    In cell B10, enter a formula using the IF and AND functions to indicate whether the revenue goal has been met that month:

    Enter the logical test using the AND function to determine if the Fundraisers amount in cell B7equals 0 and the Total in cell B8 is greater than 20000.

    If the logical test is true, display Yes (using “Yes” for the value_if_true argument).

    If the logical test is false, insert a nested IF function.
    Enter the logical test of the nested IF function using the AND function to determine if the Fundraisers amount in cell B7 is greater than 0 and the Total in cell B8 is greater than 200000.
    If the logical test for the nested IF function is true, display Yes (using “Yes” for the value_if_true argument).
    If the logical test is false, display No (using “No” for the value_if_false argument).

    Reply
    • Catalin Bombea

      February 22, 2017 at 4:07 pm

      Hi Marcie,
      Please upload a sample file on our forum (open a new topic), with your data structure and a sample result, it’s not clear what you need to do.
      Catalin

      Reply
  296. Alec Gerlach

    February 21, 2017 at 2:09 pm

    In cell H7, insert a nested function that awards the employee a 10% bonus if they were hired before 7/31/2012, a 6% bonus if they were hired on or before 7/31/2016, and 4% to employees hired after 7/31/2016.

    -can someone help me set up this function? if so, can they kinda explain how they got their answer too? im struggling with this!!!!!!!

    Reply
    • Catalin Bombea

      February 21, 2017 at 2:51 pm

      Hi Alec,
      An Index-Match formula should work:
      =INDEX({0.10,0.6,0.4},MATCH(A1,{0,41121,42582},1))
      Te formula can be inserted in any cell you need, just replace the A1 reference with the cell that holds the date to evaluate.
      Those 2 arrays with constants (in curly brackets) contains your indications: 0.10 will be returned if the date is between 0 and 41121, 0.6 will be returne if the date is between 41121 and 42582, 0.4 for dates higher than 42562.
      Make sure you have dates in correct format, not text strings. (a date is a number representing the number of days from 01/1/1900, 41121 is 7/31/2012)
      The key is the last argument of the Match function; 1 is the Match type: Less Than.

      Reply
      • Alec Gerlach

        February 22, 2017 at 1:52 am

        Thans!!!!!

        Reply
  297. Dheeraj

    February 18, 2017 at 3:52 pm

    Very good

    Reply
  298. Renee

    February 14, 2017 at 5:47 am

    Need Help with a formula….

    If J2 is > K2 up to 100, the answer is 2 but
    If J2 is > K of 101 or more, the answer is 1 or
    if J2 is < K, the answer is 3

    Reply
    • Mynda Treacy

      February 14, 2017 at 9:06 am

      Hi Renee,

      Try this:

      =IF(J2
      

      Kind regards,

      Mynda

      Reply
  299. arjun rawat

    February 10, 2017 at 12:08 am

    Name M1 M2 Project Online Total Percent Status
    Arjun 44 46 60 202 352 70.4
    Sital 27 38 73 182 320 64
    Chandan 40 47 80 275 442 88.4
    Mahesh 44 37 73 241 395 79
    Madan 33 27 70 235 365 73
    Pawan 43 47 91 225 406 81.2
    Dinesh 36 37 60 163 296 59.2
    Yogendra 26 34 51 218 329 65.8
    Aman 41 47 75 244 407 81.4
    Nabin 40 48 72 176 336 67.2 ,

    i am typing this formula=IF(AND(C3>=25,D3>=25,E3>=60,F3>=150),”PASS”,”FAIL”) for status but it says formula contains error. Could you please locate my error?

    Reply
    • Catalin Bombea

      February 10, 2017 at 6:11 am

      You have to upload a sample file on our forum(create a new topic), the formula is looking good. We have to see the file to understand what happens there.
      Catalin

      Reply
  300. Diane Awbrey

    February 8, 2017 at 8:11 am

    This didn’t work.
    =IF(ISBLANK(M2),””,IF((TODAY()-I2)>45,”Contact Requester”,””))

    It showed Contact Requestor even though M2 had a closed date and when I deleted the closed date it didn’t perform IF((TODAY()-I2)>45,”Contact Requester”,””)) part of the formula.

    Reply
    • Mynda Treacy

      February 8, 2017 at 8:57 am

      Hi Diane,

      I think I’m confused 🙂 Can you please post your question and sample file on our Excel Forum. That is the best place for support as we can actually see your file and what you’re working with.

      Thanks,

      Mynda

      Reply
  301. Diane Awbrey

    February 7, 2017 at 3:14 am

    In reference to my earlier comment….

    This almost does what I need

    =IF(AND(NOT(ISBLANK(M2))),””,IF((TODAY()-I2)>45,”Contact Requester”,””)), but if the formula is in A1 (such as a template) because I2 is blank A1 Displays “Contact Requester” until I put in an issue date, whereas I want it to be blank.

    Reply
    • Mynda Treacy

      February 7, 2017 at 8:36 am

      Hi Diane,

      Try this:

      =IF(ISBLANK(M2),"",IF((TODAY()-I2)>45,"Contact Requester",""))

      Mynda

      Reply
  302. Diane Awbrey

    February 7, 2017 at 2:00 am

    Hi I am having trouble with my formula., If M2 is blank I want to perform the following **calculation below. This part works great, what I am having trouble with is the cell M2 part of it. If M2 is not blank I don’t need to determine is today is more than 45 days (I2 contains an issue date), however if M2 is blank I need to know if it is overdue (M2 contains closed date)

    ** =IF((TODAY()-I2)>45,”Contact Requester”,””) works great

    How do I write the formula to accomplish if I2 is over 45 days and M2 is blank the cell will populate with Contact Requestor (this part works), but if M2 has a date I want A1 (cell where formula is) to stay blank.

    Reply
  303. Amanda

    February 1, 2017 at 3:20 am

    I can’t get my formula correct. What I need to do is say “if G3 is greater than today, then write RED in column D3 which is the RAG status column.

    Thanks for your help.

    Amanda

    Reply
    • Mynda Treacy

      February 1, 2017 at 10:20 am

      Hi Amanda,

      Have you tried this in cell D3:

      =IF(G3>TODAY(),"RED","")

      If that doesn’t work please upload an example to our Excel Forum so we can help you.

      Mynda

      Reply
  304. Shivam

    January 30, 2017 at 9:47 pm

    if grossprofit <=15000,then taxes = gross profit * 20 %, rest all cases Gross profit * 30%

    Reply
    • Catalin Bombea

      January 31, 2017 at 5:15 am

      Try:
      =IF(GrossProfit<=15000,GrossProfit*0.2, GrossProfit*0.3) You already written the formula, in a very close format 🙂 Replace GrossProfit with a cell reference.

      Reply
  305. Samer Saadeddin

    January 25, 2017 at 3:13 am

    Hello Guys,
    I have a formula that requires 6 out of 12 conditions to be met randomly. if I try to use the IF with AND and OR, I need to write more conditions than the Excel can provide.
    the questions is, how can I tell excel that if 6 conditions are met, then the return value would be “X”?

    Reply
    • Samer Saadeddin

      January 25, 2017 at 3:14 am

      Correction:
      How can I tell Excel to return s specific value if 6 out of the 12 conditions are met randomly?

      Reply
      • Mynda Treacy

        January 25, 2017 at 11:39 am

        Hi Samer,

        Please post your question on our Excel Forum with a sample Excel file so we can see your question in context, as I don’t understand the scenario you describe.

        Thanks,

        Mynda

        Reply
  306. Ybo Tawano

    January 19, 2017 at 6:12 pm

    =IF(B28=””,””,IF(OR(B28=B29,B28<B29),"MET","UNMET"))

    if B28 and B29 are equal, it's supposed to say "MET" but it doesn't. say B28 and B29 are both 10%, it says "UNMET" instead. care to comment? thanks

    Reply
    • Catalin Bombea

      January 19, 2017 at 8:00 pm

      Check B28 and B29, there may be more decimals than displayed. Increase the number of decimals displayed, this way you will be able to see if the results are different, or round the values: ROUND(B28,4)=ROUND(B29,4)

      Reply
  307. Leslie

    January 19, 2017 at 1:33 am

    I need help with this formula.
    If column A is “corn” then it’s d5/2.6, but if column A is “soybeans” then it’s d5/.93

    also this one:
    if column A is “cotton” and column B is “Monsanto” then it’s d5/5.55, but if column A is “cotton” and column B is “Phytogen” then it’s d5/5.11

    Any help would be appreciated 🙂

    Reply
    • Catalin Bombea

      January 19, 2017 at 8:13 pm

      Hi Leslie,
      Try this one: =IF(A5=”corn”,D5/0.93,IF(AND(A5=”cotton”,B5=”Monsanto”),D5/5.55,IF(AND(A5=”cotton”,B5=”Phytogen”),D5/5.11,0)))
      It will return 0 if no condition is met.
      Catalin

      Reply
  308. Jay

    January 18, 2017 at 8:01 pm

    Hi There
    Can one do an auto allocation?

    If A1 is > than 0 then C1 will always be 4

    Thanks

    Reply
    • Catalin Bombea

      January 18, 2017 at 8:32 pm

      What should be there if A1 is not >0?
      Put this in C1:
      =IF(A1>0,4,A1)
      If you wanted to modify cell C1 from a formula located in another cell, this is not possible, a formula will display a result only in the cell where it’s located. Only through a specific macro you can modify any cell you want.

      Reply
  309. Kevin

    January 18, 2017 at 6:39 am

    I want =IF(J4>L4,I4,K4) but if J3=L3 I want it to display TIE

    Reply
    • Catalin Bombea

      January 18, 2017 at 7:30 pm

      Try:
      =IF(J3=L3,”TIE”,IF(J4>L4,I4,K4))

      Reply