Unconditional Love for Conditional Formulas

tips

If ever there was a single function in Excel that I use the most, it’d probably be the IF function. It is a workhorse as far as I’m concerned, as it can be used in combination with several other functions for a plethora of purposes. Along with other Logical Functions like AND, OR, NOT, TRUE, and FALSE, you’ve got all the ingredients you need to create some really cool formulas that will help you not only sort your data, but can even help improve the aesthetics of your spreadsheets and help to automate the analysis of your data. Here are some reasons why I have unconditional love for conditional formulas in Excel.

What is a Conditional Formula?

A conditional formula in Excel is a formula that makes a logical test of data using the IF function. It essentially allows you to create a basic logical argument of “If (this), then (that).” Though there is an entire subset of philosophy devoted to truth-functional propositional logic, in this case, you don’t won’t have to open a textbook to develop logical arguments within Excel. One of the great benefits of using conditional formulas within Excel is that it’s pretty simple. Conditional programming is used in web design and development as well, particularly in the case where a website is visited using different browsers. I like to think of it as a point in which math, philosophy, and programming meet.

How to Write a Conditional Formula

The basic syntax of the IF formula in Excel is:

=IF(logical_test,[value_if_true],[value_if_false])

logical_test: the condition that you are checking for

[value_if_true]: the result you want if the condition is true

[value_if_false]: the results you want returned if the condition is false

As an example, you can include this formula in cell D2 to list the player with the highest score:

=IF(B2>C2,B1,C1)

You can also use the AND, OR, and NOT functions to produce TRUE and FALSE results.

=AND(B2>1,C2>1)

=OR(B4>1,C4>1)

=NOT(C3>1)

You can also use <> as an operator in your formula in place of “not equal to” like so:

=OR(B2<>0,C3<1)

Why use Conditional Formulas?

At some point in time, when you are faced with a lot of data in your spreadsheets, you may want to find a way to highlight or “filter” out some of your data based on specific criteria. For example, if you wanted to see if the value of a cell in column A is equal to the value of a cell in column B (duplicates), you can use a conditional formula in column C to give you a TRUE or FALSE result.

=IF(A1=B1,TRUE,FALSE)

This can be helpful if you are using this formula across a large range of cells and you want to be able to catch any anomalies. In another example, if you were trying to determine which cells have values of a certain range, you can also use the IF function to create a conditional formula like:

=IF(A1>3,TRUE,FALSE)

If your objective is to count how many cells fit a specific criteria (e.g. values greater than 50), you would probably be better off using the COUNTIF function. However, the benefit to using basic conditional formulas with the IF function is that you can use this formula for conditional formatting so you can highlight cells that match a criteria of your choosing.

=COUNTIF(D2:D5,B1) for cell references and numerical values

=COUNTIF(D2:D5,”Player 1″) for text vauesdon’t forget to include quotation marks if you’re referring to a text value

Applications of Conditional Formulas

Finding duplicates across rows or columns [e.g. =IF(A1=B1,”Same”,”Different”)]

Finding values in a specific range [e.g. values greater than 4: =IF(A1>4,TRUE,FALSE)]

Nested conditional formulas

Calculate different equations based on different values of a single cell

[e.g. if you’re trying to use a formula that is dependent upon conditions, you can “nest” your functions (where multple functions are used within each other)

In this example, if you were to calculate the difference of one player’s victories over the other (without ending up with negatives) and to also denote if there is a tie, you could use a formula like:

=IF(B7>C7,B7-C7,IF(C7>B7,C7-B7,”Tie”))

This formula is actually two IF formulas in one…first, you have the first IF formula IF(B7>C7,B7-C7,). However, with the value_if_false part of the equation, you include yet another IF formula: IF(C7>B7,C7-B7,”Tie”). If both the first IF formula is false (meaning that B7 is NOT greater than C7) AND the second IF formula is also false (C7 is NOT greater than B7), then the final false value carries over from the second (or in this case, inner-most) IF formula, which is “Tie.”

Applying multiple condtions in a single formula

For this example, let’s say you wanted to determine if Player 2 had a good game day by not only determining if Player 2 was a winner of the game, but that they also scored more than 3 points. You can create an IF formula with a nested AND formula so that you can narrow down your results to just the games where Player 2 met BOTH criteria like so:

=IF(AND(D2=C1,C2>3),”Good Game”,”Needs Improvement”)

In this formula, if Player 2 (cell C1) is the victor (cell D2) of the game, AND Player 2’s score (cell C2) is greater than 3, then a result of “Good Game” is produced, otherwise it will result in “Needs Improvement.” This means that if Player 1 wins, if there is a tie, or if Player 2 wins but doesn’t score more than 3 points, it will result in a “Needs Improvement” result. If you wanted to adjust the formula so that if Player 2 scores 3 points and you want a “Good Game” result instead of “Needs Improvement,” then you would adjust the > symbol to a greater than or equal to symbol >= in the formula.

=IF(AND(D2=C1,C2>=3),”Good Game”,”Needs Improvement”)

Conditional formatting

Formatting cells based on multiple criteria outside of the standard function

With Excel 2007 & 2010’s conditional formatting, you have several options available that you can use to highlight the cells that you apply the formatting to based on the selected cells’ value. But what if you wanted to format cells based on criteria that is out of the scope of the default options? You do it with a formula, of course!

In the Conditional Formatting menu, you can select the “Use a formula to determine which cells to format” rule type, which will provide you with a box for entering your formula. If, for instance, you wanted to highlight the cell listing the Game number (i.e. cells A2:A5) based on the values of the cells in an adjacent column (column D in this case which lists who the victor was), you could use a formula such as:

=IF($D2=”Player 2″,TRUE,FALSE)

Keep in mind that by default, if you click on the cell you are using in the formula, Excel will create an absolute reference (using $ in front of the column letter and row number). If you want your formula to adjust along with the cells that it applies to, you will need to remove these absolute references by simply removing the $ accordingly). Once you’ve got your formula nice and spiffed up, click on the Format button to designate the formatting you wish to apply (I’ve chosen the cell fill color of blue), then hit OK.

Since I only applied this formatting to cell A2, I want to adjust the range of this conditional format, so I will go to the Conditional Formatting menu, and under Manage Rules, I can “stretch out” the range of this condition. Also, if I wanted to apply another conditional format, say to highlight the games that Player 1 had won but in a different color, you can create a new rule with a new format by following the same steps you did before, but adjusting the formula to =IF($D2=”Player 1″,TRUE,FALSE) and changing the cell fill color (I chose red).

You can repeat the same steps to highlight the cells that have a tie (I used a purple cell fill color) with this formula in a new rule:

=IF($D2=”Tie”,TRUE,FALSE)

Make sure to adjust the ranges for the conditional formatting rules and you’ve got a color coded list that adjusts when the scores change.

For more helpful information on using conditional formulas and the IF function, check out this great post from Daniel Ferry who appears to share some love for conditional formulas as well.

Find Amanda on Google +

If you like this post, please share the love by Liking, Tweeting or +1-ing this post. Thanks in advance!

Amanda Thomas

Amanda Thomas

SEO Manager
Amanda is the SEO Manager for BRIM and is responsible for assisting the project managers with the daily tasking to the SEO team.
Amanda Thomas
Amanda Thomas
«

Comments

  1. Its very good and helpful site to learn

  2. Thanks, Noman! I’m glad you found it useful.

  3. Chris
    May 21st

    Hi there,

    I’ve been searching desperately for a way to do something on Excel that I would have thought *must* be possible using IF in some wat, but I can’t find out how. I was wondering if you could help me.

    What I want to do is apply COUNT and SUM to all and only those cells in a particular range where the cells in the first two columns in the same row have one of several values.

    For example, if and only if A3 contains any of (4, 7, 9) and B3 any of (John, Mary, Charles), then I want to apply COUNT/SUM to C3:E3. Ditto for many rows.

    So column A contains lots of different numbers (not just 4, 7, and 9), and column B contains lots of different names (not just John, Mary, Charles), and I want COUNT/SUM to apply to columns C:E in each row where column A has (4, 7 or 9) in that row and column B has (John, Mary or Charles) in that row, but not in any rows where these conditions don’t hold.

    Is this possible?! I’d be super grateful for any help!

    • Hi Chris,

      I’m not sure if this is what you’re looking for but I’ve created a mock-up spreadsheet which you can find here:

      Sum with Multiple Criteria Example

      Since I’m not sure how long your list of criteria will be (if it’s only two or three values for each column, then this would still work as long as you have room in your spreadsheet to list the criteria in columns H and I respectively), I’ve created a formula that I think will work for what you’re trying to do.

      In columns H & I you would list your criteria for columns A and B (respectively, of course). In columns C through E, you would list the values that you want to have summed. In column F, you would list the following formula:
      =IF(AND(COUNTIF($H$2:$H$31,A2)>0,COUNTIF($I$2:$I$31,B2)>0),SUM(C2:E2),””)

      This formula does a few things at once so let me just explain a little bit in case you need to make modifications:

      COUNTIF($H$2:$H$31,A2)
      In this formula, COUNTIF function is being used to check if the value in A2 matches what is listed in column H.

      COUNTIF($I$2:$I$31,B2)
      Same as the previous, this counts the number of cells that match that criteria as well.

      Since the cells that do not contain either the number or name in your criteria columns would result in a 0 value, then we’d append >0 to each of the COUNTIF functions so we can weed out the cells that don’t meet our criteria and also make the outcomes of each of these formulas either TRUE or FALSE (thanks to George Boole for Boolean logic there).

      We need to employ an IF function to make this formula conditional (we’ve got our TRUE or FALSE values for the COUNTIF functions ready), but since we need more than one criteria to be true, we’d add an AND function to the mix to ensure that whatever our result is will only occur when BOTH criteria are met.

      =IF(AND(criteria1,criteria2),value_if_true,value_if_false)

      We just replace criteria1 with COUNTIF($H$2:$H$31,A2)>0 and criteria2 with COUNTIF($I$2:$I$31,B2)>0 which leaves us to determine what result we want if the value is TRUE (value_if_true) or FALSE (value_if_false).

      Side note: you can add more criteria, but you’ll need to make more “criteria columns”.

      SUM(C2:E2)
      Since the desired value (when both the criteria are met) is to sum a range of values, we’d add SUM(C2:E2) in place of value_if_true in the formula. I assume that you want the cell to be blank (instead of 0 or FALSE) if the criteria are not met, so the value_if_false is left as “” (blank value).

      You can copy the value down the column as you see fit but don’t forget about including the $ to create absolute (fixed) cell references to your “criteria column(s)”. Also, if you want to copy the formula and you have cells that are blank, you can append an IFERROR function to the formula to create a custom error message, like so:

      =IFERROR(IF(AND(COUNTIF($H$2:$H$31,A2)>0,COUNTIF($I$2:$I$31,B2)>0),SUM(C2:E2),””),”Oops!”)

      I hope this is what you were trying to do. Thanks for commenting on the post!

  4. Chris
    May 22nd

    Oh my goodness! I’m speechless! This is so wonderfully helpful! Thank you so much for such an amazingly speedy, generous and useful reply!

    I will definitely be able to do what I want to do using this method. However, ideally I’d like something a little different. It’s not important to me (and takes up more space than I’d ideally like) to have the sum of the three cells in each row meeting the criteria expressed individually at the end of each row. I just want the sum of all appropriate cells in rows fitting the criteria to be expressed once. So in the spreadsheet which you so kindly created, that would mean F2 had the value 468.6, and the rest of column F would be blank. Can the formula you came up with be modified to give this result instead?

    Thank you so much once again!

    • Hi Chris!

      If you’re looking to conserve space on your spreadsheet, have you considered hiding column F and including a SUM formula in G2 (where F2 would be if it weren’t hidden) of =SUM($F$2:$F31) instead?

  5. Paul
    May 30th

    Hi Amanda!

    Im trying to reformat a bunch of cells. (67,000 of em). Some have the format 030, some 30, some 3. I want them all to be three digits, with leading zeros. I.E. 3 becomes 003, 30 becomes 030, and 030 stays as is. What is the code for “count how many characters are in the cell, and blah blah blah” ?

    thank you!!!!

    • Hi Paul,

      You have a few options, depending on what you want to do. It sounds like you may have different number formats set to the values if you have a preceding 0 in front of the number. If you end up wanting to copy+paste the preceding 0s, then you may need to convert the numbers to text. You can do this with a formula in an adjacent cell:
      =TEXT(A1,”000″)
      (where A1 contains the value you want to convert to the three digit format)

      Otherwise, if you just want to keep the values the same, you can apply a custom number format by going to Format Cells>Custom and in the Type field, list 000.

      If you want to copy and paste those values in the same format, you can copy+paste special and select the “Values and number formats” radio button.

      I hope this helps.

  6. Drew
    June 26th

    Hi Amanda,
    I’m trying to format a row based on multiple cell values in a column.
    IE Column B values = eg 057,037, 075…, then format the row color.
    So far i have “=INDIRECT(“B”&ROW())=57″ then a color format.
    What do i do to get these variables into 1 formula without having to create a separate rule for everything?
    Thanks heaps!

    • Hi Drew,

      I’m hoping that I understand what it is that you’re trying to do with the conditional formatting but here’s a potential solution. If you have a specific number of cells in, let’s say column B (B1:B5), then you could try using a formula in your conditional formatting rule to:

      =IF(OR(ROW($A1)=$B$1,ROW($A1)=$B$2,ROW($A1)=$B$3,ROW($A1)=$B$4,ROW($A1)=$B$5),TRUE,FALSE)

      and apply that to an area such as $A$1:$E$100. You, of course, could modify the formula for different columns, but would a formula such as that work for you?

      • Drew
        June 27th

        Thanks Amanda,
        I’m not sure about that solution though.
        What I’m trying to do is:
        We have a large number of buildings to manage ~100
        each with different number of floors.
        The buildings are grouped into colored zones and numbered, not consecutively.eg 022, 035, 057 yellow, 021, 043 red, 042 blue.
        I have columns for buildings, floors, rooms, IP addresses etc, and i need to sort.
        I would like to create a formula for each group of buildings to highlight the rows according to each colored zone.
        Cheers Drew

        • Hi Drew,

          Sorry for my misunderstanding. It sounds like you’d still need to use separate rules given that you’ll be working with separate formats (e.g. yellow, red and blue). I think I might be a little confused because of the use of the INDIRECT function you’re using.

          You could try layering the conditional formatting so the conditional formatting applies to the same area for each separate format that you specify, but it sounds like that’s what you were trying to avoid by trying to avoid making separate rules. Do you think that adding an OR function to specify a group cells where your desired variables are located would help?

          Something akin to:

          FOR YELLOW RULE:

          =IF(OR(ROW()=VALUE($A$1),ROW()=VALUE($A$2),ROW()=VALUE($A$3),ROW()=VALUE($A$4),ROW()=VALUE($A$5),ROW()=VALUE($A$6),ROW()=VALUE($A$7),ROW()=VALUE($A$8),ROW()=VALUE($A$9),,ROW()=VALUE($A$10)),TRUE,FALSE)

          Where the Yellow property cells can be found in a specified range of cells such as A1:A10…for example:
          A1 = 022
          A2 = 035
          A3 = 057
          etc.

          FOR RED RULE:

          =IF(OR(ROW()=VALUE($A$11),ROW()=VALUE($A$12),ROW()=VALUE($A$13),ROW()=VALUE($A$14),ROW()=VALUE($A$15),ROW()=VALUE($A$16),ROW()=VALUE($A$17),ROW()=VALUE($A$18),ROW()=VALUE($A$19),,ROW()=VALUE($A$20)),TRUE,FALSE)

          Where the Red property cells can be found in a specified range of cells such as A11:A20…for example:
          A11 = 021
          A12 = 043
          etc.

          FOR BLUE RULE:

          =IF(OR(ROW()=VALUE($A$21),ROW()=VALUE($A$22),ROW()=VALUE($A$23),ROW()=VALUE($A$24),ROW()=VALUE($A$25),ROW()=VALUE($A$26),ROW()=VALUE($A$27),ROW()=VALUE($A$28),ROW()=VALUE($A$29),,ROW()=VALUE($A$30)),TRUE,FALSE)

          Where the Red property cells can be found in a specified range of cells such as A21:A30…for example:
          A21 = 042
          etc.

          If you want to add more cells in which the variables are set for each color, just add them to the end another OR condition/logical [ e.g ,ROW()=VALUE($A$1)] before the second close parenthesis before “,TRUE” .It’s up to you if you want to use absolute references in the cell’s location.

          That does requrie a lot of setup at first, but I hope that might help make the process quicker in the long-run for you. There are a lot of excellent Excel forums where you might find some additional help. You might want to give these a try if you need more help:

          http://www.excelforum.com/
          http://www.mrexcel.com/forum/forum.php
          http://www.ozgrid.com/forum/
          http://www.excelguru.ca/
          http://www.excelbanter.com/

          Cheers! :)

          • Drew
            July 2nd

            Thanks Amanda,
            That looks good.
            I think i was over complicating things for myself a bit though.
            I ended up creating another column for zones and using 1 formula for each color.
            eg; =INDIRECT(“B”&ROW())=”YLW”
            and apply to =$1:$1048576
            This works for all 9 zones.
            Don’t know why i didn’t think of it first :)
            Cheers

          • Amanda Thomas
            July 2nd

            Happy to hear you found a solution. :)

  7. Toby
    June 27th

    Hello Amanda,
    Thank you for all of the valuble information above. I have a question about conditional formatting.

    I am trying to incorperate a CF that if a cell does not contain a formula, the cell will then turn red. I know how to incorperate the CF if the cell has a formula, but I really need the opposite. Pa..Pa..Pa.. Please help me.

    • Hi Toby,

      It looks like you’re going to need to use a macro to create a formula that will allow you to determine whether a cell has a formula or not. There’s a really great example of this which can be found on Excel Banter here (http://www.excelbanter.com/showthread.php?t=259907 – it’s the third posting) but essentially, you’d need to open your VB Editor (Alt+F11), insert a new module, paste the following script:

      Function IsFormula(r As Range) As Boolean
      IsFormula = Left(r.Formula, 1) = “=”
      End Function

      Close out VB Editor and use this formula in your CF:

      =isformula(A1)

      Note: you could also use an IF formula such as =IF(isformula(A1)=TRUE,TRUE,FALSE)

      Make sure that the area in which the formula applies to is correct (when you modify the area it sometimes messes with the formula in the CF if you aren’t using absolute references so watch out for that). (e.g. Applies to: =$A$1:$B$10)

      If you want the opposite effect (essentially, you want the CF to apply when the condition is FALSE – essentially when the cell is NOT a formula), then you just swap out the FALSE and TRUE in the CF formula like so:
      =IF(isformula(A1)=TRUE,FALSE,TRUE)

      I hope that helps you!

  8. Stephanie
    July 6th

    I want to include cells in a calculation, only if another cell has a check in it. I have a list of employees who belong to two different unions. I calculate their weekly hours and wages total the hours and wages for the month and then need to make calclations based on which union they belong to. So, some of the wages and hours I would like to include in certain calulations and others I want to exclude. How can I do that? I was thinking of having a column of “Union 1?” and “xing” if employee part of that union. How do I wrtie a formula to accomodate this? Or do you have a different idea?

    • Hi Stephanie,

      If you’re only working with 2 unions, you can specify the Union that each employee belongs to by creating an additional column (let’s pretend it’s column A) where you would list that Union name (we’ll use “Union 1″ and “Union 2″ as examples). If your desired formula is in Column B, for instance, you would create your conditional formula to handle different calculations based on the value in Column A.

      e.g. if the hours worked are being listed in Column C and you want Union 1 to have wages calculated at a rate of $8 per hour and Union 2 to have wages calculated at a rate of $8.25 per hour, you would create a formula in cell B2 like:

      =IF(A2=”Union 1″,8.00*C2,IF(A2=”Union 2″,8.25*C2,”No Union”))

      This allows you to specify that if that particular employee is “Union 1″, they will have their hours (in column C) multiplied by 8.00, but if they are listed as “Union 2″, their hours will be multiplied by 8.25. However, if for any reason you accidentally don’t have a Union listed (which you’ll definitely want to be able to catch) then the default value will be set to “No Union” if A2 doesn’t equal “Union 1″ or “Union 2″ (which includes blanks).

      If you want to go a little more advanced, you can modify the formula so that whenever the wage amount changes (I’m using California & Nevada minimum wage amounts as an example since will occasionally change), you can update 2 cells and the rest of your formulas will update with it (which is known as using a dynamic formula).

      If you list Union 1’s hourly wage in cell A1 (8.00) and Union 2’s hourly wage in cell B1, then you’d use this modified formula in cell B2:

      =IF(A2=”Union 1″,$A$1*C2,IF(A2=”Union 2″,$B$1*C2,”No Union”))

      Please keep in mind that the dollar signs ($) in the cell location are important for making sure that your formulas don’t adjust when you copy and paste the formulas to other cells (what is called an absolute reference).

      If you want to find even more helpful Excel tips and conditional formula advice, there are some great Excel-specific forums that can provide some advice as well:
      http://www.excelforum.com/
      http://www.mrexcel.com/forum/forum.php
      http://www.ozgrid.com/forum/
      http://www.excelguru.ca/
      http://www.excelbanter.com/

      I hope this helps.

  9. Hi Amanda,

    I hope you can help me. I am working on a spread sheet and need to link the color of a group of cells to an outcome in one single cell. For example –

    If AG5>0 then I need C5-O5 to be colored blue (filled), if not then I need C5-O5 to stay clear (unfilled).

    Cheers Amanda.

    Leo

    • Hi Leo,

      It sounds like a job for conditional formatting. However, since there’s a few ways to configure this (depending upon how you want the formatting/coloring of cells to work), I’m going to give some steps that I think will help accomplish your goal.

      Step 1: Select cell C5

      Step 2: Go to the Conditional Formatting drop down menu (in the home ribbon if you’re working with Excel 2007)

      Step 3: Select the option of “New rule” (should be third from the bottom)

      Step 4: A window will appear (called the New Formatting Rule), select the bottom most option which should be titled “Use a formula to determine which cells to format”

      Step 5: In the field under “Format values where this formula is true”, list this formula:
      =IF($AG5>0,TRUE,FALSE)

      Step 6: Go to the “Format…” button and specify the formatting that you want (e.g. in the Fill tab, select Blue as the color and hit “OK”)

      Step 7: If the formatting that shows up in the Preview window is correct, hit “OK”

      Step 8: Not quite done yet…you still need to specify the area in which you want to apply this conditional formatting. Go back to the Conditional Formatting drop down menu

      Step 9: Select “Manage rules…” from the Conditional Formatting drop down menu (Should be the very bottom)

      Step 10: from the Conditional Formatting Rules Manager window, you will need to specify the area in the “Applies to” field. List the following area:
      =$C$5:$O$5

      Step 11: Hit “Apply” to view the results and if it’s good to go, hit the “OK” button.

      If you want to apply this conditional formatting to several rows (not just row 5), then you’ll want to adjust the area in step 10 (e.g. =$C$5:$O$100).

      I hope this helps. Cheers!

      • hi friends,
        I encountered a typical problem for calculating the interest for a borrower.Let me explain how our interest application takes place.We are offering products at daily reducing balance interest which operate as follows,for e.g I lend you Rs.100000.00 on 1st April 2012 at 10% rate of interest.If you repay Rs.60000 on 20th April 2012,I will apply the interest in following manner,apply the interest for Rs.100000 for 19 days and after repayment apply the interest for Rs.40000 for the remaining 11 days.Please note that interest application will takes place at the month end.Balance at the month will be arrivedasfollows.
        Balance=100000+((100000*10*19/36500)+(40000*10*11/36500)=RS.100641.00.
        If there is more than one repayment then we have to calculate interest considering all the repayments.Similarly I have the option of increase or decrease the interest rate with in a month.If I change the interest rate with in a month we have to calculate the interest at two different rates.Now the problem arises,think of the situation in borrower repaid the part of the loan in 4 different days and also I changed the rate of interest 3 times.
        Considering these 2 variables(rate of interest change and different repayments at different dates) please give me a solution using MS-excel (formula for incorporating the change of interest rate with in a month and multiple repayments).If you give me a formula which is capable of handling infinite changes then it is a great help to me.

        Regards

        Balamurugan R

  10. Dave
    July 11th

    Is there a formula that can be used in conditional formatting that will allow me to shade the cell if it contains “-7-” within the number. for example if cell A1 has 12-8-00234-7 it would remain unshades, however if cell A3 had 12-7-00234-7 it would become shaded with a color I choose. Thansk, for your time

    • Hi Dave,

      You can actually do this in two ways. The quickest option would be to highlight the cells that you wish to create the rule for, go to the Conditional Formatting drop down menu (on the Home tab), select “Highlight Cells Rules” and then select “Text That Contains…” which will take you to a small window where you can list *-7- in the field on the left. (Don’t forget to include the asterisk before you type in -7- since Excel will mistake the hyphens as a subtraction operator [minus sign for a formula] instead of treating it as text)

      If you wish to create your own custom formatting, just select the “Custom Format…” option from the drop down menu and create your custom formatting.

      Alternatively, you can create a custom Conditional Formatting rule using some nested formulas which support the FIND function. Highlight cell A1, go to Conditional Formatting > New Rule > Use a Formula to Determine Which Cells to Format, and in the formula field list the following formula, then hit OK.
      =IFERROR(IF(FIND(“-7-“,A1,1)>=1,TRUE,FALSE),FALSE)

      This essentially is checking to see if it can find “-7-” within cell A1, which if it can (which would mean that the result would be greater than or equal to 1), the formatting rule will be TRUE. Otherwise, if the value is less than 1 or, if there’s an error (which is the case when “-7-” cannot be found), it will result in FALSE (which will mean that the conditional formatting will not apply).

      You will still need to go back to the Conditional Formatting drop down but this time go to Manage Rules and then change the range of the cells that you want the conditional formatting to apply to (e.g. =$A$1:$A$100).

      I hope this helps!

      • Dave
        July 13th

        Thanks so much, Amanda. I just realized I failed to mention I am stuck working with 2003 at the office so I don’t think the first approach will work (sounds like it is based on the later/recent version of office 2007/2010). I tried the “=IFERROR….” formula in the 2003 version of conditional formating but it wouldn’t work.

  11. Youssef
    July 17th

    Hey Amanda

    Thank you for all the great stuff you’re posting out here, if it’s possible I’d appreciate your help with something that buzzles me

    I need to make an If formula in which the value would turn into 2 if the sum of two cells in two different columns would be one of a list of text values that I have in another column,

    I used the following formula but it does not work

    =IF(CONCATENATE([@Eye]&[@[Actual Post Op sr. code 1 ]]=Sheet2!S$2:S$26),2,1)

    As I tried to put the range of values that I want the concatenate result to match in the range s2:s26 in the second sheet

    can you help me with that ??

    • Hi Youssef,

      It sounds like you’re trying to accomplish a few things at once and although I’d love to help you with getting this formula working for you, I think you might be able to find more help and a quicker response if you were to submit your query to one of the Excel forums that I’ve listed above. If the following information doesn’t help, I’d recommend going to one of those forums which have a lot of really knowledgeable contributors.

      Assuming this solution works for you, I just want to give a quick rundown of what this is doing (in case there’s a step here that you need to modify to get the results you’re looking for):

      Assuming that your first two columns (where you wish to concatenate the two cells) are columns Q and R and your other column (that lists the values that you want to check for) is in cells S2 through S26, then in a cell on row 2 (e.g. cell P2), try this formula:

      =IF(MATCH(Q2&” “&R2,
      =IFERROR(IF(MATCH($Q2&” “&$R2,$S$2:$S$26,0),2,””),””)

      What this does is it checks to see if there’s a match of Q2 combined with R2 (with a space in between in case you need it) within the cells in S2 through S26 (the dollar signs are important to keeping those cell references from changing when you copy the formula to other cells). If there is a match, the output will be the number 2, otherwise it will be blank (“”). In the case that the value cannot be found, you will get an error message so the IFERROR formula wraps around that formula to have the value of that formula default to “” if there is an error. You can set the error message however you want though.

      If that doesn’t work, I’m sure you can find a solution fairly quickly on one of these forums:
      http://www.excelforum.com/
      http://www.mrexcel.com/forum/forum.php
      http://www.ozgrid.com/forum/
      http://www.excelguru.ca/
      http://www.excelbanter.com/

  12. Harshal
    July 18th

    hi amanda
    i need some help in my query i have a table with 4 colums & i want to compare that example colA => colB or ColA =< ColC then show me "B" OR "S" in different colum.
    this is what i have done =IF(C10=D10,"S",C10-D10) but i can get only 1 condition solved but other i am not getting the other one can u help me in this

  13. Harshal
    July 18th

    i have also try this formula but the result is not coming proper
    =IF(OR(C10=D10,C10=E10),”S”,”B”)
    at every colum its saying “B” except where the first condition is true over here i want if none of the condition r true its should not show me any of them so what should i do

    • Hi Harshal,

      I’m not sure if I understand what you’re trying to do but here’s an idea…if that doesn’t work, you might be able to find some help on one of these great Excel forums if you need more help troubleshooting the formula:
      http://www.excelforum.com/
      http://www.mrexcel.com/forum/forum.php
      http://www.ozgrid.com/forum/
      http://www.excelguru.ca/
      http://www.excelbanter.com/

      If you are comparing the values in columns C, D and E as your formulas would indicate, then if you’re trying to get two separate values (e.g. “S” or “B”) but you want those values assigned when specific criteria are met (meaning that if the criteria isn’t met, that you get neither the “S” or “B” values), then you need to add an additional condition to the formula to create the “default”. In the formula =IF(OR(C10=D10,C10=E10),”S”,”B”), this is checking to see if EITHER (meaning that only one of these criteria have to be met in order for the condition to be TRUE) C10=D10 or C10=E10, that the result would be “S”. Any time that both of those critera are not met, you will keep getting the “B” result. If your intention is to assign “S” as a result if C10=D10 and assign “B” as the result when C10=E10, then you’d want to use this formula:

      =IF(C10=D10,”S”,IF(C10=E10,”B”,””))

      What this is doing is first checking to see if C10=D10. If that is true, the result is “S”, if THAT result is false, then it uses another IF formula to check to see if C10=E10 and if that is true, the result is “B”…if that is false, however, it results in a blank (“”) value.

      Please note that because the first condition of “S” appears, if C10=D10 AND C10=E10, you will have the result of “S” since it appears first in the formula. If you wanted the formula to result in SB if BOTH of those criteria were met, then the formula would need to be changed to:

      =IF(IF(C10=D10=E10,”SB”,IF(C10=D10,”S”,(IF(C10=E10,”B”,””)))

      Where it checks first to see if C10=D10=E10, then checks for the other two criteria and so forth.

      I hope this solution helps. If not, you might find some help with nested IF functions here as well:
      http://office.microsoft.com/en-us/excel-help/if-HP005209118.aspx

      • Harshal
        July 23rd

        hi thanks for ur help i have done what u have given & i even got it working but their is problem in working with too many if conditions it only take 2 time if condition working. i have done this & i got it working (=IF(C59=D59,”S”,IF(C59=E59,”B”,”NA”)))
        but when i am combining more if condition then its not working (=IF(C82=D82+0.05,”B”,”NA”))) this r the other conditions which i am combing with the first formula but not working this is my table content
        C D E
        496 502 496
        288.3 309.85 288.3
        141.1 141.1 134.9
        73.65 73.65 71
        74.5 74.55 72
        87.35 90 87.3
        when i am writing these in same formula its not working.

        • Harshal
          July 24th

          =IF(C59=D59,”S”,IF(C59=E59,”B”,IF(C82=D82+0.05,”B”,”NA”))

          • Harshal
            July 24th

            IF(C59=D59,”S”,IF(C59=E59,”B”,=IF(C82=D82+0.05,”BUY”,”NA”))

  14. Hi Amanda,
    It is excellent topic in excel that I am looking for. I wondering if those excel formula will work on Google Open Office?
    Thanks.

    • Hi Andrew!

      There are a lot of similarities amongst functions/formulas between Microsoft Excel and Open Office Calc. Though it’s been quite a while since I last used Calc, I was able to find a little info about the formula/function compatibilities between Excel and Calc. Here’s a link if you wanted to see more about it:
      http://www.worldstart.com/incompatible-formulas-openoffice-calc-excel/

      According to April, the difference between Calc and Excel formulas is the usage of a colon (:) as opposed to a comma (,). I hope this answers your question. Also, speaking of Google, there is a lot of cross-compatibility of functions between Excel and Google (Google Docs, soon to be Google Drive) Spreadsheets as well and here’s a link to Google’s list of functions as well:
      https://support.google.com/docs/bin/static.py?hl=en&topic=25273&page=table.cs

  15. sparky
    July 20th

    Hi Amanda,
    I am trying to create a spreadsheet that I can use as a template. In the first part of the spreadsheet I enter a company name, ie: Johns pets. Further down in the spreadsheet I don’t want to re enter the company name or their address. I have the address(s) located in another area of the spreadsheet to use as a reference. Is there a way that when I type in the company name at the start it will automatically populate the address from the other location on the spreadsheet in the desired cell? I have about 6 different companies I am working with. So I just want to be able to type the company name and have the appropriate address appear in the cell I want. So I think it is a nested If function but I just can’t seem to make it work.

    I would appreciate any feedback.

    • Hi Sparky,

      For the first step of allowing you to use the autocomplete function:
      if you are listing the company name in the same column but you have blank cells that separate the two areas where you plan to list the company name, then you won’t be able to utilize the autocomplete feature of Excel. If the company names are listed in the same column, but in separate rows, then if you can fill in the truly blank cells with something as simple as a space (something that wouldn’t be visible but acts as a filler for the cells in between your two sections of company names), that will allow you to workaround this issue (you can type in a space in the first blank cell and copy and paste it down the column as a shortcut to this process).

      However, if that data is NOT in the same column, there’s not a whole lot else that you can do without setting up Data Validation. Since Data Validation can be configured to allow you to pick from a drop down menu (from a list that you specify – you can learn more about creating a drop down list with data validation here: http://office.microsoft.com/en-us/excel-help/data-validation-1-control-user-choices-with-lists-in-excel-HA001087228.aspx), the AutoComplete function won’t work unless you also used a work around which ozgrid.com explains here: http://www.ozgrid.com/Excel/autocomplete-validation.htm.

      Given all the extra work involved with the latter solution, if it’s at all possible to just fill in the blank cells with spaces (assuming the company name is in the same column), I’d recommend doing that. Now for the next step of having the address information pull as well…

      You can use a VLOOKUP OR an INDEX/MATCH formula to pull that data based on the company name that you’d be filling out (with autocomplete). Since using a VLOOKUP or INDEX/MATCH formula requires some configuration based on the location of your data, you’ll need to customize the formula to your spreadsheet. Fortunately, Microsoft has more information about how to setup these types of dynamic searching formulas here:
      http://office.microsoft.com/en-us/excel-help/dynamic-searching-using-vlookup-match-and-index-HA001154902.aspx

  16. Vicky
    July 24th

    Hi, hope you can help, there’s some great advice on this page!
    I have a spreadsheet to monitor when reports are due, sent and received and need to highlight certain things. Some of these are probably quite simple but I’ve been looking at it too long ang making it too difficult!

    B1 has the current month.
    Column C = month report due – if it is same as current month – AMBER
    – if it is before current month – RED
    However if col D has been filled in, no formatting required.

    Column D = date report sent – if col C is red/amber and cell is blank – RED
    When cell populated, normal formatting.

    Column E = date report returned – if Col C is populated with current month and cell is blank -AMBER
    If it is prev month and cell blank – RED
    If cell populated, and it was late (I.e more than 30 days after date in col D) – YELLOW

    I’d appreciate any help you can give me on this……
    Thanks :)

    • Hi Vicky,

      In interest of keeping things a quick and simple as possible, here’s a rundown of the things I’d recommend you do:

      1) If you haven’t already, you can use a formula in your column B of =MONTH(TODAY()) which will update automatically each time you open your workbook.

      2) You will need to setup several conditional formatting rules…if you need help figuring out how to add a rule for conditional formatting, please refer to this article:
      /blog/using-conditional-formatting-to-save-time-and-effort

      a) From cell C2, create a conditional formatting rule of:
      =IF(AND(MONTH(C1)=B1,D1″”),TRUE,FALSE)

      Then format this color to AMBER

      b) From cell C2 (again), create a conditional formatting rule of:
      =IF(AND(MONTH(C2)<B2,D2″”),TRUE,FALSE)

      Then format this color to RED

      c) From cell D2, create a conditional formatting rule of:
      =IF(AND(D2=””,OR(MONTH(C2)=B2,MONTH(C2)<B2)),TRUE,FALSE)

      Then format this color to RED

      d) From cell E2 (again), create a conditional formatting rule of:
      =IF(AND(MONTH(C2)=B2,E2=""),TRUE,FALSE)

      Then format this color to AMBER

      d) From cell E2 (again), create a conditional formatting rule of:
      =IF(AND(MONTH(C2)<B2,E2=""),TRUE,FALSE)

      Then format this color to RED

      e) From cell E2 (last time), create a conditional formatting rule of:
      =IF(AND(E2″”,E2>=(D2+30)),TRUE,FALSE)

      Then format this color to YELLOW.

      3) Once you’ve completed the task of creating your 6 conditional formatting rules to cell C2, you can go to your Conditional Formatting Rules Manager (Conditional Formmatting > Manage Rules) and then edit the area in which the formatting applies for each rule (in the Applies to field).

      I hope this helps!

  17. Harshal
    July 25th

    hi amanda
    i am having problem in finding that is colA value is Greater then the Value in ColB by .15 i am not getting the formula working
    if(A2=C2+.15,”Buy”,”NA”))can u plz help me out
    ColA 43.00 54.00
    ColB 43.15 59.00
    ColC 46.00 53.85

    • Hi Harshal,

      The forums I mentioned earlier in this post are the best place to ask these types of questions. In your first formula, were you trying to reference the same row? (in the formula you list both rows 59 and 82). If the following formula doesn’t work, I recommend using one of the forum links listed above to find a solution that works best for you.

      =IF(C59=D59,”S”,IF(C59=E59,”B”,IF(SUM(C59+0.05)=D59,”BUY”,”NA”)))

      As for your second question, have you tried moving around parts of the formula (and using parenthesis for blocking out the SUM formula when adding 0.15 to the value of the appropriate cell). Something more like:

      =IF(SUM(A2+0.15)=C2,”Buy”,”NA”)

      for example? Since the forums listed above are frequented by several people throughout the day, you might find it more convenient to go through the forums for this type of help. Best of luck with your formulas!

      • Harshal
        July 26th

        thank you amanda i think u have solved my problem mostly where i was stuck. i got it working thanks a lot for ur help

  18. Sherri
    July 25th

    Hi there. I am trying to figure out a formula where it will work for 2 different cells and produce different values but the same formula is used.
    For example, right now this is what I have for each cell
    =IF(E9-F9+G9-H9>0,E9-F9+G9-H9,0) and =IF(E9-F9+G9-H9<0,-(E9-F9+G9-H9),0)

    Any ideas?

    • Hi Sherri,

      Are you trying to combine those two formulas together? If so, have you tried nesting the IF formulas, like so:

      =IF(E9-F9+G9-H9>0,E9-F9+G9-H9,IF(E9-F9+G9-H9<0,-(E9-F9+G9-H9),0))

      I hope that helps.

  19. Lucy
    July 25th

    I am interested in checking a single cell for the color blue. We have a spreadsheet where we assign specific tasks to certain individuals. When they are assigned it colors the cell blue. On a second sheet, I want to check for the color blue and then print that individuals name which is in column A.

    I can’t seem to make it work – I know it should be easy, Just can’t make it work. Any ideas?

    • Hi there,

      Unfortunately, there isn’t a function (that is built into Excel) that can be used to determine the color of a cell. However, you can add a macro that will allow you more or less to create your own custom function. You can see an example of this at the Excel Forum here: http://www.excelforum.com/excel-formulas-and-functions/619938-if-cell-is-red.html

      I must warn you that there’s a bit of VBA programming involved (and you’d need to use a macro-enabled workbook) which, if you’re really not a fan of doing, you may need to change the architecture of your workbook so that you employ an additional column which is used as a sort of placeholder so you can use formulas that apply to that (and if you like the aspect of having custom formatting, you can use conditional formatting based on that additional column as well). I hope this helps you Lucy.

  20. Amy
    July 25th

    Hi! Can you tell me if it’s possible to leave the cell with the previous value if the condition comes back with FALSE when using an IF formula?

  21. Nick
    July 26th

    Hi Amanda,

    I am trying to use more then 1 =IF(AND( combos in the same line to capture the different input criteria and output criteria that i need. For example i want to write something which will be based on a excess amount or say 100 or 250, then i want to put the claim amount in, dependant on the excess and the claim amount i then want to either multiply the claim by 25% until it hits a certain claim level, i am able to write the formula for 1 excess criteria but for some reason cannot link more then 2 in the same formula line.

    This is what i have written for the excess of £150 and depdendant on the claim being £600 or more after you input 150:

    =IF(AND(B19=150,C19<600),C19*25%,"150")

    This is exacytly what i want to do, however, i want to include more excess criteria with different claim outputs dependant on the excess. I hope this makes sense?

    Thanks

    Nick

    • Hi Nick,

      Since I’m not entirely sure what it is that you’re trying to do, here are a few potential solutions:
      1) Creating nested IF formulas – that seems like the simplest solution, but it also sounds like you’re trying to do a lot of things at once. Nevertheless, here’s a link about nesting IF formulas: http://spreadsheets.about.com/od/tipsandfaqs/qt/nested_if.htm
      2) Using a SUMIF formula – if you’re trying to create a formula that works off of several criteria, this is the way to go: http://support.microsoft.com/kb/275165
      3) Using a What-If analysis – if you’re trying to “backtrack” a formula (by creating criteria and have Excel populate the values that are needed to get to that result), then a What-If analysis might be more appropriate (these are particularly popular for loan calculators and such. Here’s a link to Microsoft’s intro do What-If analyses: http://office.microsoft.com/en-us/excel-help/introduction-to-what-if-analysis-HA010342628.aspx

      If those aren’t helpful, you can always hit up one of the many Excel forums (like the ones listed above in the comments on this post) or you might be able to find a template online that already has the formulas in place as you need it. I hope these help you accomplish your goal.

  22. Hello Amanda

    I need a formula to look at a cell and based on the value in it look at another table and put a value in another cell.

    I tried

    =IF(ISNUMBER(SEARCH(“7″,D4)),$C$141

    but cannot use this for more than two variants.

    I basically have a table of numbered priced options which can be incorporated.

    SET PRICE
    7 £32.31
    8 £31.06
    9 £13.40
    11 £17.00
    12 £6.50
    13 £4.75
    14 £5.25
    16 £3.50
    17 £16.00
    18 £32.90
    19 £382.46

    and another table where you enter the SET number and I want it to tell me the value.

    Hope that makes sense

    Mark

    • Hi Mark,

      I hope I understand what it is that you’re trying to accomplish. It may be something as simple as using a lookup formula. You have two (main) options: 1) VLOOKUP or 2) INDEX/MATCH. I prefer the latter, and I’m not the only one…Charley also sees the value in using INDEX/MATCH over VLOOKUP in his post here: http://exceluser.com/blog/1107/why-index-match-is-far-better-than-vlookup-or-hlookup-in-excel.html.

      However, getting back to the issue at hand, if you use and INDEX/MATCH formula in your second table, you can use it to reference the first table and “match” the Price associated with the Set number in your second table. I realize that seems a bit complicated, but once it’s setup it’s actually quite easy. Here’s what you can do:

      1) From your first table (I’m just going to call it Table1 for the time being), make sure you have all your data and that you don’t have any duplicate Set numbers.
      2) In your second table (let’s call it Table2), in the Set column, put any number (7 for example)
      3) In the Price column of Table 2, add this formula:
      =INDEX(Table1,MATCH(Table2[[#This Row],[SET ]],Table1[[SET ]],0),2)
      Note: Your tables need have matching names, so you will either need to change your tables’ names or modify the formula accordingly.

      Now, if you try this formula with a blank in the Set column, you’ll probably get a #N/A error. To get rid of this error, wrap the formula in an IFERROR formula, like so:
      =IFERROR(INDEX(Table1,MATCH(Table2[[#This Row],[SET ]],Table1[[SET ]],0),2),””)
      (this will make any cells that have errors default to blank [“”])

      If you REALLY want to get fancy, you can swap out the final ,2) of the formula (which simply says to lookup the value in the 2nd column) for another MATCH formula so you don’t have to worry if/when you insert any columns in your spreadsheet at a later time.
      =INDEX(Table1,MATCH(Table2[[#This Row],[SET ]],Table1[[SET ]],0),MATCH(Table2[[#Headers],[PRICE]],Table1[#Headers],0))

      I hope this proves helpful to you.

  23. Smita
    July 30th

    hello,
    i am looking for a solution in excel, will you please help me?
    Question is – i want to convert some specific cell value according to my choice i.e. when someone enter the time <9:30 it will automatically converted into 9:30.
    Its urgent……

    • Hi Smita,

      Are you trying to create a formula to extract the time (e.g 9:30 from a cell value that says “<9:30") or are you trying to create a default value for any time that is earlier than 9:30? Forums, like the ones I've listed above, are better for urgent questions but here are some formulas that I hope might help you:

      If you're trying to just remove the greater than and less than signs from the cell, I recommend using some nested SUBSTITUTE formulas, such as:
      =SUBSTITUTE(SUBSTITUTE(G10,"”,””)

      However, if you’re trying to create a conditional formula that defaults to 9:30 in the case of a value being less than 9:30, you might want to try this formula:
      =IF(TIMEVALUE(TEXT(A1,”h:mm”))<TIMEVALUE("9:30"),TEXT("9:30","h:mm"),TEXT(A1,"h:mm"))
      (where A1 contains the cell where the time is listed)

      If you still need help, please refer to one of the forums listed earlier in the comments of this post.

  24. Kaveri
    July 30th

    Hi Amanda,

    I’ve been trying to figure something out with excel and even bought a book hoping it would help.. but havent figured out how to do it yet. Your help would be invaluable!!

    What I’m trying to do is this:

    I have two columns of data with many blank rows in between. I want to put all the data together in a neat looking range, without all the empty rows between them. I dont want to do it manually because it is something I will have to do over and over as this is a huge set of data. I also can’t delete entire rows as there is other data in the worksheet.

    Please help!!

    Many many thanks!

    • Hi Kaveri,

      I have run into a very similar problem before and I’ve found two solutions: 1) use a pivot table (if you don’t mind a small amount of manual work) or 2) you can try using a few formulas that will remain in hidden columns that will allow you to “filter” out any blank cells. Taking away some of the ideas behind what I have dubbed the “Best Rank Function” (I know, kinda corny, but I couldn’t help myself), you can accomplish this with the ROW, SMALL, INDEX and MATCH functions. You can find an example of this in the following spreadsheet download found on this post:
      /blog/making-best-rank-function-in-excel

      (The downloadable spreadsheet link is towards the bottom and the formulas that would accomplish this are found on the tab titled ListWithoutBlanks)
      I hope this helps!

  25. Robin
    July 30th

    I need a formula that can match information in a cell in column A with information in column b and append those matches in a single cell. Is that possible?

    • Would something like an INDEX/MATCH formula work? You could try something like:
      In cell C1:
      =A1&INDEX($A$1:$B$200,MATCH(A1,$B$1:$B$200,0),2)
      This would essentially add the value of cell A1 to the result of an INDEX/MATCH formula which checks for (and returns the value of) a match for cell A1 in cells B1:B200.

      If you need to add a dash or a space between the two values (or any other type of text), you can modify the beginning to =A1&”-“&INDEX…

      I hope that was what you were looking for.

  26. MMohammed Ali
    August 1st

    I have a schedule for the employees, and it contains seven columns as 7 days in a week. If any range of the columns contains text ” OFF”, i need to see the eighth column contain the result 0 or if all the range of columns is blank the result in the eighth column should be “8”. Please help me to rectify the problem.

    Thanks.

  27. MMohammed Ali
    August 1st

    Its solved.

  28. Kellis
    August 1st

    Hi Amanda
    I have a If formula =IF(OR(J:J=”16:00″),”Yes”,”No”)
    which when data is pasted into the s/s it will return a yes or no in the column to advise whether the job was carried out with normal working hours. I need to copy the formula down my spreadsheet however when I get to the rows where data has not been entered yet the formula defaults to Yes. Can you suggest how I can have the formula in the column but stay blank until other data in the row is entered.
    Thanks

  29. Philip
    August 4th

    Hi Amanda,
    Your very helpful responses to others have inspired me to see if my own challenge can be solved! I’m using Excel 2010 and already have an extremely simple solution, but it involves a lot of copying and changing of the formula between cells, so there may be another way of achieving the same.
    I want to test the content of one cell and depending on the result, then have the sum of a calculation placed into 1 of 4 cells or options.
    For example:
    In L18 I have the formula: =IF($B18=1,$J18,0);
    In M18 I therefore have the formula: =IF($B18=2,$J18,0);
    and so on.
    In each case I am testing for the existance in column B for 1,2,3 or 4 (and it could increase). The sum of J18 then gets put in the appropriately headed columns in L – O (Option 1, 2, 3, etc.).
    I wondered whether a nested LOOKUP command might work, but couldn’t get my head around it!

    Many thanks

  30. Amanda Thomas
    August 7th

    Hi Philip,

    There are a few ways that you can go about doing this in a more “automatic” fashion. My first suggestion would be to list the numbers in a header row (e.g. L1, M1, N1, O1, etc. = 1, 2, 3, 4, etc.). If you use this method, you can simply use the following formula in L18 (and then copy to the other cells):

    =IF($B18=L$1,$J18,0)

    Otherwise, if you’re limited on space and cannot place these values in a header row, you can also utilize the COLUMN function to populate the numbers that you’re checking column B for. Since each column has a numerical equivalent, you can subtract a specific value to get to the value you need (and then copy across the row)…here’s what I mean:

    =IF($B18=COLUMN(L18)-11,$J18,0)

    Since COLUMN(L18) would equal 12 (since L is the 12th column [starting from column A] in the workbook), if you subtract 11 from the value of COLUMN L, you will be able to check column B for a value that is relative to its column value. (I hope that makes sense how I worded that).

    Good luck with that formula!

  31. Kenny
    August 7th

    Hi Amanda,

    Please help!!! I want to replace the values returned with colour in the below.G-green, R-red and A-amber

    =IF(AI11>AT11,”G”,IF(AI11<AT11,"R",IF(AI11=AT11,"A")))

    Many thanks

    • Amanda Thomas
      August 7th

      Hi Kenny,

      You’ll have to create separate Conditional Formatting rules for each color. In cell AU1 (or whichever cell you want to start with), go to the Conditional Formatting menu, select “New Rule”, select the “Use a formula to determine which cells to format” option, and in the formula bar, add this formula:
      =IF($AI11>$AT11,TRUE)

      Before you hit OK, select the “Format” button and select the Fill tab and choose the color Green. Hit OK to get out of the format window, and hit OK one more time to set your Conditional Formatting rule. Repeat the same steps only for your “Red Rule, use the formula:
      =IF($AI11<$AT11,TRUE)
      (set the Formatting to a red fill color)
      And for the "Amber Rule", use this formula:
      =IF($AI11=$AT11,TRUE)
      (set the Formatting to an amber fill color)
      Now that you have those Conditional Formatting rules established for that cell, you can now go back to the Conditional Formatting drop-down menu, select the "Manage Rules" option. From there, you can expand the area where you want the Conditional Formatting to apply to.

      I hope this helps!

  32. Jason
    August 14th

    Hi Amanda,
    I saw that you know quite a bit about excel formulas and was wondering if you could help me out.

    I’m trying to get cells in 1 sheet to copy over rows from another sheet which have a particular colour coding in one of the columns for each row.
    So I can have all the data from the rows which contain for ex. JUN into once section on another sheet. They all have their own colour code but I can’t figure out how to get the entire row to copy over based on the colour in that single column.

    If that makes sense, could you help me?

    • Amanda Thomas
      August 16th

      Hi Jason,
       

      I’m not sure if this would help accomplish what you’re trying to do but there are two ways of going about this:
      1) without macros
      2) with macros
       

      If you wish to go the latter route, there’s a really cool example workbook that you can download from Ron de Bruin here:
      http://www.rondebruin.nl/copy5.htm
      (There’s a link towards the bottom for “Download Example workbook”)
       

      You’d still have to modify your workbook and the VB Code to accomodate your current data, but it’s pretty cool. You can click a button to copy the data from a specific criteria in the “master sheet” to another sheet (either an existing sheet, or a new sheet that you can designate the name for). My description does it no justice I’m afraid, so if you don’t mind getting your hands in a little VBA code, that might help you. The downside to this is that in order for the list to be generated, you need to manually press the button from the “master sheet”.
       

      Otherwise, if you’re willing to put in a little more effort into building formulas into separate sheets, you can follow these steps:
       
      1. Name your “master” sheet “MasterSheet” (or you can use the name you have currently, but just keep in mind that the formula I will end up using below will be utilizing the “MasterSheet” name)
       
      2. Make sure you add a “helper column” to your “master sheet” so you can use this to query information into your “monthly sheets”. E.g. adding an additional column (either before or after your main data) that will use the formula in step 2…
       
      3. You’ll need to denote individual entries for each month so you can use a formula to generate these unique values based on the month and the “count” (number of other entries of the same criteria). Here’s an example of said formula that generates the month and “count” number based on the date which is found in column F:
      =MONTH(F1)&”.”&SUMPRODUCT(- -(F$1:F1″”),- -(MONTH(F$1:F1)=MONTH(F1)))
      (FYI, there are two minus signs (-) but they shouldn’t be separated with spaces in your formula…I just had to do that to prevent the two minus signs from forming a long hyphen)
      Now that you have your unique values associated with each row (there’s a method to the madness of using the month number in the aforementioned formula), you can then use this number as a reference to a different sheet that you can use to pull that data from
       
      4. Create a new sheet (let’s just name it June).
       
      5. In cell A1 on June’s sheet, type 6/1/12. (If you don’t like to see the date in that format, you can go to Format Cells > Custom Format and then list “mmmm” in the Type field and then hit OK.)
       
      6. In cell A2 (so just under the month/date that you listed in A1) add the following formula:
      =MONTH(A$1)&”.”&ROW()-1
       
      7. Copy the formula down as far as you need in column A (it never hurts to have more than you need just in case).
       
      8. In cell B2 (right next to where you added that formula in step 5), add the following formula:
      =INDEX(MasterSheet!$A$1:$L$288,MATCH($A2,MasterSheet!$L$1:$L$288,0),COLUMN()-1)
       
      9. Copy the formula from B2 across however many columns and rows that you need.
       
      10. If you’re tired of getting error values because the “record” does not exist, then modify your INDEX/MATCH formula with an IFERROR formula like so:
      =IFERROR(INDEX(MasterSheet!$A$1:$L$288,MATCH($A2,MasterSheet!$L$1:$L$288,0),COLUMN()-1),””)
       

      You can copy that sheet into as many other sheets that you need but remember to change the date in cell A1 (where the month is being referenced). Also, you might need to expand the length of the formula where it references $L$288 to any length of your choice…like $L$5000 for example. I hope that helps!

      • Jason
        August 16th

        Thanks for the info but it doesn’t really solve my problem.
        I need it to copy over a row of data dependent on the month it picks up in the middle of a string of letters. For example like this:AB0123JUN12

        I want it to pick up the month inside this string which would be JUN and then copy that entire row over to another sheet.

        Thanks for the help though. If you can solve this little problem It would save me a tonne of time.

        • Jason
          August 16th

          Probably forgot to add, all the months will be going into a single sheet for a large set of data. Otherwise i’d end up with about 30 different sheets.

          • Amanda Thomas
            August 17th

            I’m sorry that didn’t help you with what you’re trying to do. I misunderstood that there was other text/numbers included within the cell where the month was located and not just the month itself. If you’re just looking to extract the month from the text, there’s a few ways to do this. Since the first hurdle to overcome is trying to get that month number out of the string, I’m going to just list a few options of how you can extract the month number from the string’s text. Once you have that step out of the way you can then use lookup/reference formulas (INDEX/MATCH or VLOOKUP) which use the month number to match the rest of the data in that row (when it comes time to copying it to another location – whether it’s the same sheet or a different one).
             

            Option 1: If your string always consists of the same structure (e.g. XX####MON##), then you can use this formula to pull the month number:
            (assuming the month is listed in cell A1)
            =MONTH(DATEVALUE(TEXT(RIGHT(LEFT(A1,9),3),”mmmm”)&” 1″))
             

            Option 2: If you want to utilize a series of nested conditional formulas (which can get really messy really quick), you can also use this formula (this might be a better option if the strings that you referred to don’t always match the same structure):
            =IF(NOT(ISERROR(FIND(“JAN”,A1,1))),1,IF(NOT(ISERROR(FIND(“FEB”,A1,1))),2,IF(NOT(ISERROR(FIND(“MAR”,A1,1))),3,IF(NOT(ISERROR(FIND(“APR”,A1,1))),4,IF(NOT(ISERROR(FIND(“MAY”,A1,1))),5,IF(NOT(ISERROR(FIND(“JUN”,A1,1))),6,IF(NOT(ISERROR(FIND(“JUL”,A1,1))),7,IF(NOT(ISERROR(FIND(“AUG”,A1,1))),8,IF(NOT(ISERROR(FIND(“SEP”,A1,1))),9,IF(NOT(ISERROR(FIND(“OCT”,A1,1))),10,IF(NOT(ISERROR(FIND(“NOV”,A1,1))),11,IF(NOT(ISERROR(FIND(“DEC”,A1,1))),12,””))))))))))))
             

            Option 3: If you have a list of the months in a separate section in your sheet, you can use an array formula that will lookup the month number attributed to each month. Let’s say you have your months listed in chronological order (e.g. JAN, FEB, MAR…) in cells C1:C12, the month # in cells D1:D12 (e.g. 1, 2, 3…) and your string is located in cell A1. You could utilize this array to find what month is found within the string based on the values that you set in cells D1:D12:
            =INDEX(C$1:D$12,MAX(IF(ISERROR(FIND(C$1:C$12,A1)),-1,1)*(ROW(C$1:C$12)-ROW(C$1)+1)),2)
            (this is an array formula, so that means you have to hit CTRL+SHIFT+ENTER when you’re done entering the formula)
             

            Those three options allow you to pull the month number for the string. While that may not seem to help much, you can then use the month number to match corresponding data in the same row by using a lookup formula like INDEX/MATCH or VLOOKUP. You’ll still need to create unique values for these month values which is where you might need an additional “helper” column next to your month # column. In that secondary “helper” column, you’d need to include a formula that will generate a unique number which will prevent the INDEX/MATCH or VLOOKUP formula from pulling data from the same row. This is where a COUNTIF formula can come in handy as you can append the “count” or occurences of rows that contain that month.
             

            Please let know if that is more on-track with accomplishing your goal. Also, if these ideas are way off track or don’t help, I certainly don’t wish to waste your time or energy so if you can find a more suitable remedy a little quicker by opening a thread in one of the forums listed in the comments above, please don’t hesitate to do that either. I hope whatever route you take, you find the solution you’re looking for.

  33. Rishav
    August 15th

    Dear Amanda,
    Am a new visitor to this blog. Delighted to see such helpful reverts from your side. Sure it takes efforts to timely reply back. Thnx..
    Can you please let me know if we can use text in “IF” function. eg. =IF(F7=Book,TRUE,FALSE).
    If not, then is there any way we can do it.
    Thnx.

    • Amanda Thomas
      August 16th

      Hi there Rishav,

      Have you tried adding quotes around the word Book?
      e.g. =IF(F7=”Book”,TRUE,FALSE)

  34. Heidi
    August 15th

    Hi Amanda,

    I have the conditional formula =IF(C3=6,(B3+(7*25)),IF(C3=12,(B3+(7*51)))) however if I don’t have data it reports as FALSE. How can I make it report blank. I’ve tried “” but that doesn’t seem to work.

    • Amanda Thomas
      August 16th

      Hi Heidi,
      Have you tried adding the quotes within the second IF formula?
      e.g. =IF(C3=6,(B3+(7*25)),IF(C3=12,(B3+(7*51)),””))

      You might have to manually edit the double quotes since it seems that the reply above is adding two different characters for the double quotes. If you have any issues with using that formula, that would be the reason (Excel should highlight the double quotes for you if that is the case). I hope that helps!

      • hi amanda,
        i would like to ask you what formula can i use in making descriptive grades to be seen in one cell depending on its numerical grade. like if one student got 74 and below its equivalent is B or beginning, 75 to 79 D or developing, 80 to 84 AP or approaching proficient, 85 to 89 P or proficient and A for 90 and above..please help me,thanks alot…

        • Amanda Thomas
          August 28th

          There’s a few ways of doing this, but for a really quick copy and paste formula, if you enter this formula into the cell where you want the grade to populate (assuming that the score is listed in A1), you should get your grade values:
           
          =IF(A1>=90,”A”,IF(A1>=80,”B”,IF(A1>=70,”C”,IF(A1>=60,”D”,IF(A1″”,”F”,””)))))
           
          If you want to include the +s and -s, you could also use this (incredibly long) conditional formula:
           
          =IF(A1>95,”A+”,IF(A1=90,”A”,IF(A1>=90,”A-“,IF(A1>85,”B+”,IF(A1=85,”B”,IF(A1>80,”B-“,IF(A1>75,”C+”,IF(A1=75,”C”,IF(A1>70,”C-“,IF(A1>65,”D+”,IF(A1=65,”D”,IF(A1>60,”D-“,IF(A1>55,”F+”,IF(A1=50,”F”,IF(AND(A1<50,A1″”),”F-“,”N/A”)))))))))))))))
           
          I’m a big fan of using lookup formulas but in this case if you’re using the same grading numbers, instead of having to create a separate list of your grades and their corresponding values, this might end up being a quicker, more space saving solution.
           
          If that doesn’t help, here’s a link to a page that uses a lookup formula to pull the grades based upon their values in a list of scores and their corresponding grades:
          http://www.wfu.edu/~matthews/plus_minus/excel_example.html

  35. Heidi
    August 16th

    That worked! Thanks so much.

  36. Greg
    August 16th

    Hi, I am looking to create a formula that takes a balance one month, subtracts if from the previous months and returns the amount of the variance in red IF it exceeds both a dollar limit as well as a percentage values. How would I do this? Thanks.

    • Amanda Thomas
      August 28th

      Hi Greg,
       
      If I were to try to accomplish that same effect, here’s how I’d do it:
       
      I would create a list of the months in a column and add some formulas to calculate the balance on a monthly basis (which subtracts the monthly total from the previous month). Then, if you want the color to change, you can add some conditional formatting to make the cells red based on the criteria (whatever the dollar amount or percentage) that you specify.
       
      Let’s it break down step-by-step:
      (Please modify column/cell references as needed of course)
       
      1. Add transaction amounts listed in column A2:A100
      2. Add dates of corresponding transactions in column B2:B100
      3. Add Month list in column C2:C13
      – 3.1: in order for this to work with the formula I’m listing, you’d need to list the date of the months (e.g. 1/1/2012) which, of course Excel will transform into the serial number. For easier readability, I recommend that you reformat those cells containing the months by going to Format Cells > Custom > …then specifying “mmmm” (without the quotes) in the Type field. You can format it however you prefer but it is important for the formulas to work that the months are not text values.
      4. In cells D2:D13, you will be listing the variance but before you do that, you’ll want to list your starting balance first. Add the starting balance in cell D1.
      5. I’m going to assume that this list starts at January, so in cell D2 (next to January in column C), add this array formula:
       
      =D1-SUM(IF(MONTH(B$2:B$36)=MONTH(C2),A$2:A$36,0))
       
      Remember, arrays require you to hit CTRL+SHIFT+ENTER when you’re done adding the formula to the formula bar. If you don’t do this, the numbers will be skewed. You’ll know that you’ve done it right when the formula shows squiggly brackets. Just remember that if you go in to edit this formula, you have to remember to still hit CTRL+SHIFT+ENTER each time you edit it).
       
      6. Copy that array down to cell D13 (or however long you intend to create your list).
       
      7. Now that you have your balances listed next to each month, you can create a conditional formatting rule to highlight red when the value(s) in cells D2:D13 are of a certain amount or percentage. You can do this by:
       
      – 7.1: first selecting cell D2
      – 7.2: going to Conditional Formatting (from the Home tab) > New Rule > Use a formula to determine which cells to format
      – 7.3: in the formula field (labeled “Format values where this formula is true”), use this formula:
      =IF(OR(D2>80,D2>PRODUCT(D1*0.9)),TRUE,FALSE)
      (by the way, this is assuming that the dollar amount is greater than 80 and the percentage is set at 90% or 0.9)
      – 7.4: hit the Format… button and set your formatting to your desire, then hit OK
      – 7.5: hit OK from the “New Formatting Rule” window.
      – 7.6: go back to the Conditional Formatting drop down, but this time select “Manage Rules” (at the bottom)
      – 7.7: in the “Applies to” field of your recent conditional formatting rule, change the area where the formatting is applied to by either listing =$D$2:$D$13 or by manually selecting the area (if the formatting doesn’t appear by default, use the drop down menu at the top titled “Show formatting rules for” to find the formatting rule you just created.
      – 7.8: hit OK
      – 7.9: if ever you need to modify the criteria for your conditional formatting, just go back to the “Manage Rules” window and double click the rule and modify accordingly.
       
      I hope this helps!

  37. TWang
    August 17th

    Hi Amanda,
    I have a problem, and I’m not sure if it can be solved by a conditional formula or not.
    I’m trying to figure out how to set a price for one item in order to maintain a specific profit margin for a group of items.
    In Column A I have quantities of items
    In Column B I have the sale price for each individual item
    In Column C I have the total sale price for each item (A*B)
    This pattern repeats itself in several different categories
    In Column D I have a total sale price for each category
    At the bottom I have a total cost (calculated in a different area)
    Below that I have a total sale price (total of all column D)
    Below that I have a profit margin (=1-total cost/total profit)
    All of this works fine.
    My problem is, I want excel to automatically adjust the sale price of just one item in order to ensure I always maintain the same profit margin, compensating for any adjustments that may take place in all the other sale prices.
    Is this a conditional formula? And if so, what’s the formula?
    Thanks!

  38. TWang
    August 17th

    Hi Amanda,
    I’m afraid I wasn’t clear about the layout of my table described above:

    A1-A6: quantities of items
    B1-B6: sale price of individual items
    C1-C6: total sale price of items ie. =A1*B1
    D1: total of rows 1 and 2 – =SUM(C1:C2)
    D3: total of rows 3 and 4 – =SUM(C3:C4)
    D5: total of rows 5 and 6 – =SUM(C5:C6)
    D7: total cost (calculated elsewhere)
    D8: Total Sale Price – =SUM(D1:D6)
    D9: Profit Margin – =1-D7/D8

    The various sales prices will vary, so I want a formula for B2 so that no matter what the other prices are, B2 will always adjust in order to ensure the profit margin remains constant.

    Is this possible?

  39. andie
    August 20th

    Hi Amanda,

    I need a formula and for the life of me can not figure out how to do it (its Monday morning!!) in column A i have a budget amount and in column b i have the invoiced amount how do i get a cell in column B to highlight itself if there is a price difference between the budgeted amount and the invoiced amount??

    Thanks
    Andie

    • Amanda Thomas
      August 28th

      Hi Andie,
       
      If you’re trying to have the invoiced amount highlight when it is not equal to the budgeted amount, you can use conditional formatting by selecting one of the cells containing your invoiced amount (let’s pretend it’s located in cell B2), go to Conditional Formatting (from the Home tab) > New Rule > Use a formula to determine which cells to format … then in the field titled “Format values where this formula is true“, list the following formula:
       
      =IF(A2-B20,TRUE,FALSE)
       
      Set your formatting by hitting the Format button and changing the cell or font color (or both). Hit OK from the Format Cells window and hit OK again from the New Formatting Rule window.
      Go back to the Conditional Formatting menu and select “Manage Rules” from the drop down and change the range of the rule in the Applies to field (so that it applies to all of your “invoice cells”) and hit OK.
       
      I hope that helps!

  40. UDYAD
    August 21st

    Hi Amanda,

    I have a question on Rank … While using the Rank() function it skips a rank no. just after an unique rank … But if I use the rank function along with count() then it provides me the simple ranking i.e. 1,2,3 and so on. But I want equal Rank for Unique data… What should I do ?

    • Amanda Thomas
      August 29th

      Hi Udyad,

      Are you referring to how the RANK function will identify some “ranks” as the same number (e.g. if there is a tie between two ranks, it only reports one and skips the other)? If so, one of the ways you can get around this is by adding a “helper column” in an adjacent column that appends the ROW of the corresponding value to create a “unique value”. So, if you had your data in cells A2:A100, you could add a “helper column” in cells B2:B100 where the formula would follow a similar structure as this:
       
      =A2+PRODUCT(ROW(A2)*0.000001)
       
      Then, in your ranking formula, you would reference your “helper column” (cells B2:B100) data instead, since this has the “unique values” (from having appended a fraction of a whole number that is based upon the cell’s row number) and should result in breaking any ties that you might have.
       
      For more about breaking ties, Debra Dalgleish at Contextures has a really great post about how to use the RANK function and breaking ties:
      http://www.contextures.com/excel-functions-rank.html
       
      Also, I wrote a post about using the “Best Rank” function that has a free spreadsheet download link if you were interested as well:
      /blog/making-best-rank-function-in-excel
       
      I hope that helps!

  41. Roy Middleton
    August 21st

    Hi Amanda

    I’ve only just discovered this website and I have to say that I’m positively overwhelmed at the brilliant help and subsequent results.
    Needless to say I also have something that I could do with some help with.

    Each time a piece of equipment, under warranty, fails it’s recorded by serial number, time, location and failure mode (1,2 or 3). I use 2010’s CF duplicates to identify any repetition. I need to do this a maximum of 3 times, i.e. identify the equipment failure on each of three occasions so that on the third occasion a deeper investigation will result. Now the tricky bit, for me anyway, how do I do this and automatically send the information to a separate worksheet within the same workbook so that the failures are identified in one place? The resultant worksheet will have the equipment serial number, time, location and failure mode of each of the three occasions that it has failed.

    • Amanda Thomas
      August 29th

      Hi Roy,
       
      Thanks for the kind words. I don’t think I’ve mentioned this, but given that there might be some people reading this, I want to encourage everyone to send updates if the solutions worked because it might help others with their spreadsheet queries. Getting back to what you’re working on, in your first worksheet (I’m going to call it “Test”), I assume that these failure modes are being denoted in an adjacent cell as the rest of the equipment info (serial number, time, location, etc.) but for the sake of simplicity, I’m going to pretend that these failure modes are listed in cells D2:D100 and please forgive me if I’m a bit off in understanding how the failure mode/occasions process works. If you’re only trying to have the equipment listed in the separate workbook (which I’m going to call “Investigate” for this example) that it only includes the equipment information (serial number, time, location, etc.) for equipment that has occurred three times in your “Test” worksheet, I would suggest employing a “helper column” in your “Test” worksheet that provides a value based on whether or not a particular piece of equipment has popped up three times in your “Test” worksheet. Then, you can use those values in conjunction with some lookup formulas (my favorite being INDEX/MATCH combinations) to pull the corresponding equipment information into your “Investigate” worksheet.
       
      Here’s how I’d recommend doing that:
       
      Pretending that the serial numbers are listed in cells A2:A100, time in B2:B100, location in C2:C100 and failure mode in D2:D100:
      In cell E2 (which will need to be copied down to E100 or however long you need to adjust the formulas for):
       
      =IF(COUNTIFS(A$1:A2,A2,B$1:B2,B2)>=3,ROW(A2),””)
       
      (What this formula is doing is listing the row number of a piece of equipment that has shown the same serial number [of course] with the same failure mode value either 3 or more times. Keep in mind that the $ in the cell references is key here because this creates an absolute reference so that the COUNTIFS formula starts from cells A1 and B1 and doesn’t change to A2/B2 when you copy the formula down)
       
      Now, here’s where you can use those values in column E to populate the serial number, time, location and failure mode in the “Investigate” worksheet:
       
      in cell A1 (if you change this starting row, you will need to adjust the formula provided below) of the “Investigate” worksheet, use this formula:
      =IFERROR(SMALL(Test!E2:E100,ROW()),””)
       
      You should see the row number of the first piece of equipment whose corresponding cell in column E is generating the row number in. I realize this is a bit confusing (I know I had a hard time first trying this type of setup), so I hope this isn’t too vague.
       
      In cell B1 in the “Investigate” worksheet, to pull in the serial number (assuming it is found in column A), you would use this formula:
      =IFERROR(INDEX(Test!A1:E100,A1,1),””)
       
      In cell C1 in the “Investigate” worksheet, to pull the time value for the corresponding equipment, you’d use this formula:
      =IFERROR(INDEX(Test!A1:E100,A1,2),””)
       
      In cell D1 in the “Investigate” worksheet, to pull the location for the corresponding equipment, you’d use this formula:
      =IFERROR(INDEX(Test!A1:E100,A1,3),””)
       
      (Notice how the value is increasing just after the A1 cell reference. If you copy and paste this formula to other columns, you will need to update this value accordingly)
       
      I hope this helps. Please feel free to update the comment if/when it works. Thanks Roy!

  42. Kat
    August 23rd

    Hi, Amanda,
    I’m trying to see if the results of a formula in a cell equal the value in a cell in another worksheet of the same workbook. For example, if the results of the formula in A30 [=SUM(A25-(A20+A21+A22))] equal a value in a cell of another worksheet in the same workbook [Worksheet2!$B$30], then I’m using an IF function to return “yes” if they match, and “NO” if they don’t [=IF(A30=Worksheet2!$B$30,”Okay”,”NO”)]. But since it returns “NO” even if the values match, I assume it’s trying to match a formula with a number instead of the RESULT of that formula with a number.
    But how do I get my conditional formula to work?
    Thank you for any help you can give me!
    Kat

    • Amanda Thomas
      August 29th

      Hi Kat,
       
      I’m not sure why it would be defaulting to “NO” given that your conditional formula is written correctly. Have you tried nesting the formula that is listed in cell A30 into your conditional formula to see if you still get the same result? For example:
       
      =IF(SUM(A25-(A20+A21+A22))=Worksheet2!$B$30,”Okay”,”NO”)
       
      Are any of the cells being registered as numbers instead of text? I’ve had that happen on occasion, but if that is the issue, then modifying the conditional formula by wrapping the Worksheet2!$B$30 reference in a VALUE() formula should fix the problem. e.g.
       
      =IF(A30=VALUE(Worksheet2!$B$30),”Okay”,”NO”)
       
      Also, I’m not sure if this applies either, but Excel will often default to rounding up values of cells if they contain fractions as small as one-ten-millionth (e.g. 0.0000001) so if for any reason any of the values are getting that granular in their calculation, that might also create a false negative in the conditional formula as well. Though it would look like the whole number from the surface, if you see the additional decimals in the formula bar when you have those values selected, that might be a possible culprit to this conditional conundrum.
       
      If it is an issue with the rounding of values, you can try using one of the many ROUND functions like:
       
      =IF(ROUND(A30,2)=ROUND(Worksheet2!$B$30,2),”Okay”,”NO”)
      (you can change the number of digits that you want to round the values up to by swapping out the 2 in the formula)
       
      I hope one of these things helps solve the issue. Best of luck!

  43. Michael
    August 23rd

    Hi Amanda,
    First off you’re Awesome! Thank you helping everyone. I have a question. What would the formula be if I have three Cells with numbers in them (the #’s will be between 0-10) and I want the cells to be highlighted if there is a difference of 3 less or more? i.e. cell A1 is 6 cell B1 is 5 and cell C1 is 8… Since there is a difference between B1 and C1 of 3 I would need A1, B1, and C1 to be highlighted.

    • Amanda Thomas
      August 29th

      Thank you Michael. That sounds like a job for conditional formatting! So, just to make sure I understand, you want columns A, B and C to be highlighted when there is a difference of 3 between any/all of the values in columns A, B and C?
       
      If so, I’d recommend starting off by:
       
      1. Selecting cell A1
      2. Go to Conditional Formatting (from the Home Tab) > New Rule
      3. Go to Use a formula to determine which cells to format
      4. In the field titled Format values where this formula is true, list this formula:
      =IF(OR(ABS(A1-B1)>=3,ABS(B1-C1)>=3,ABS(A1-C1)>=3),TRUE,FALSE)
      (by the way, the ABS formula is creating an absolute value of the number, so negatives are given a positive value for the purpose of comparing it to 3 in this formula)
      5. Select the Format button
      6. Designate your formatting for the highlighting (e.g. Yellow cell fill color), then hit OK
      7. Hit OK again to finish up your conditional formatting rule.
      8. Go back to the Condtional Formatting drop down menu but this time select “Manage Rules”.
      9. In the Applies to field, highlight the area were you want this to apply (e.g. =$A$1:$C$100)
      10. Hit OK.
       
      I hope that helps do the trick!

  44. Jenn
    August 23rd

    Help! I am trying to create a formula from an excel spreadsheet and cannot come up with the correct one. My column F has numbers and I want to add up all of the numbers that are greater than 1, PLUS, I want to include only those numbers associated with Melamed from column L. Can you do a formula to only look at Melamed in column L and then add the numbers greater than 1 from column F?

    • Amanda Thomas
      August 30th

      Hi Jenn,

      Have you tried using a SUMIF formula like:
      =SUMIF(L$1:L$100,”Melamed”,F$1:F$100)

      I hope that helps!

  45. Ritika
    August 24th

    Hello Amanda,

    I have to test few conditions. Could you please help me with this. It goes like this:

    I need to check following:
    – if value in the cell A1 is Low and the value in B1 falls between 0-2 then the value in c1 should be 1.
    – if value in the cell A1 is high and the value in B1 falls between 0-2 then the value in c1 should be 2.
    -similarly if value in the cell A1 is low and the value in B1 falls between 2.1-5 then the value in c1 should be 3.
    -if value in the cell A1 is high and the value in B1 falls between 2.1-5 then the value in c1 should be 4.

    I have to perform this comparison for many ranges
    5.1-8
    8.1-13
    so on.

    Please help me. Its a bit urgent.

    Thanks in advance

    • Amanda Thomas
      August 30th

      Hi Ritika,
       
      If you’re in a rush for a solution, I recommend you submit any queries to one of the forums listed in the comments above – that’s what they’re there for and I don’t mean to take away from the great folks over there who have been a great help to myself :). However, given the conditions that you listed above, you could create one monster of a conditional formula if you try to do this in one formula, so instead I would recommend you alter some of the structure of your worksheet so you can include a list of your values and their ranges and then use an INDEX/MATCH array formula to generate the corresponding value based on your list of values.
       
      For example, if you create a separate area in cells D1:D100 listing Low and High
      D1 Low
      D2 High
      D3 Low
      D4 High
      D5 Low
      D6 High
      D7 Low
      D8 High
      …etc.
       
      Then, in cells E1 through E100 list the maximum values for the cut-off for each value:
      E1 2
      E2 2
      E3 5
      E4 5
      E5 8
      E6 8
      E7 13
      E8 13
      …etc.
       
      Then, in cells F1 through F100 list the output values you want for each of those cut-offs:
      F1 1
      F2 2
      F3 3
      F4 4
      F5 5
      F6 6
      F7 7
      F8 8
      …etc.
       

      These two areas will serve as your “table” for which you will be able to lookup the values. Now, in cells C1 through C100, you can use this array formula to lookup the value based on the table you’ve created:
       
      =INDEX(D$1:F$100,MATCH(A1&INDEX(E$1:E$100,MATCH(B1,E$1:E$100,1)),D$1:D$100&E$1:E$100,0),3)
       
      Remember to hit CTRL+SHIFT+ENTER when you’re done entering this formula for it to work properly. You can find more information about array formulas in Excel through search engines and also from this post:
      /blog/array-formulas-in-excel-saving-time-and-space-in-your-spreadsheets
       

      I hope that helps.

  46. SHISHIR
    August 25th

    which formula use for return a given answer if there are two conditions are true, using excel

    ex.

    sheet1!A2=Sheet2!A2:A2000 & Sheet1B2=Sheet2!B2:B2000

    Return Sheet2!E2

    • Amanda Thomas
      August 30th

      Hi Shishir,
       
      I hope I understand the question and this is the sort of formula you’re looking for:
       
      =IF(AND(sheet1!A2=Sheet2!A2,Sheet1!B2=Sheet2!B2),Sheet2!E2,””)
       
      This will leave the cell blank if both conditions are not true. Also, I don’t know if you were trying to ensure that all the values within A2 through A2000 and all the values in B2 through B2000 in Sheet 2 were ALL TRUE. If so, you would need to modify your formula like so:
       
      =IF(AND(sheet1!A2=Sheet2!A2,IF(COUNTIF(Sheet2!A2:A2000,sheet1!A2)=COUNTA(Sheet2!A2:A2000))=TRUE,Sheet1!B2=Sheet2!B2,IF(COUNTIF(Sheet2!B2:B2000,sheet1!B2)=COUNTA(Sheet2!B2:B2000))=TRUE),Sheet2!E2,””)
       
      I realize it’s a bit long so adding anything more to that formula might be a nightmare, but if you want a “custom” error message if those conditions are not met (instead of a blank value: “”), you can add text like “ERROR” instead of the trailing “” in the formula above.
       
      I hope that answers your question.

  47. Lewis
    August 25th

    Hi,
    I need a formula that ensure when A54 reads ‘South’ and B54 reads ’10’ the value will be cell D56 from ‘tables and factors’ sheet.
    Thanks.

    • Amanda Thomas
      August 30th

      Hi Lewis,
       
      Please try:
      =IF(AND(A54=”South”,B54=10),’Tables and Factors’!D56,””)
       
      That will leave the cell blank if either A54 does not equal “South” and B54 does not equal 10.
       
      I hope that helps!

      • Amanda Thomas
        August 30th

        Sorry, Lewis. I forgot to also mention that you might need to edit the quotation marks in the formulas as the comment converts the quotes to similar but not identical characters.

  48. tmk
    August 27th

    Hi, Amanda

    Sum of Cells A1:A5 = A6, needs to be 300 (not 300

    =IF(OR(SUM(A1:A5)=300,SUM(A1:A5)))

    Answer I get is TRUE or FLASE; instead I need the values to be shown in the cell A6.

    Any solution this problem please

    Regards
    Krishna

    • Amanda Thomas
      August 30th

      Hi Krishna,
       
      I think I understand what you’re trying to get as your result but just so I make sure I avoid any confusion, the solution below is assuming that you’re trying to create a formula that will give you the value of A6 if the sum of A1 through A5 is equal to 300. Is that correct? If so, try using this formula:
       
      =IF(SUM(A1:A5)=300,A6,””)
       
      This will leave the cell blank if the sum of A1 through A5 does not equal 300. If you want to change the result from blank to something else (like, say SUM(A1:A5) for example), you could use a formula like:
       
      =IF(SUM(A1:A5)=300,A6,SUM(A1:A5))
       
      I hope that helps!

  49. Janet Jenkins
    August 28th

    Hello Amanda!

    Lots of good information on here!

    I am working in Excel 97-2003 and I need to do conditional formatting for certain number values. For instance, I would like to have the numbers 1,3,7 turn red. The cell will never consist of more than one decimal. I am limited on my conditional formatting with only 3 different conditions allowed. So I would like use one condition for more than one value. I know there has to be a formula for this!

    Thank you!

    • Amanda Thomas
      August 30th

      Hi Janet,
       
      I will be honest, it’s been years since I’ve worked with Excel 97-2003 so I’m a bit rusty, but can you add a formula for your conditional formatting rule that uses the OR function? For example:
       
      (I’m assuming that the data you want to check is in cell A1 and the conditional formatting rule is set to “Formula Is”)
      =IF(OR(A1=1,A1=3,A1=7),TRUE,FALSE)
      (and, of course, set your formatting)
       
      Any time you need to add more values to the conditional formatting, you’ll need to add:
      ,A1=
      (then, of course, whatever number you need) but you’ll need to do that for every number within the AND formula (before the ,TRUE,FALSE) section)
      If you need to change the cell reference, make sure you use absolute or relative references as needed or else the formatting might get a little funky because it’s referencing the wrong cell(s) when you extend the range of your conditional formatting rule.
       
      I hope that helps as a workaround.

  50. David
    August 29th

    Hi Amanda,

    I’m not sure if this is possible and its been bugging me for a little while now, and have been googleing it too (how I found this page :) )

    Essentially what I am trying to do is to create a formula to use a certain word that is in one particular cell, but it is moving as data is added, and use that cell as a reference to then use the OFFEST command to select a cell which will be back a column and down differing amount of rows depending on what I’m trying to do.

    Basically to put into a formula what I’m trying to do “{=OFFSET(VLOOKUP(4:4,”MONTH”),4,-1,1,1)}” This however obviously doesn’t work because I have not completed the VLOOKUP (however I am not wanting VLOOKUP to work in that way.

    I want it to search for the cell containing the word “month” (on row 4) and use that cell as a reference (or if you can think of a better way to do this) and move back a column and down 4 rows (in this instance)

    It is confusing and there may be an easy way to do this but I just am having a brain fart obviously and unable to sort this.

    Many thanks for any time spent even just reading this.

    David

    • Amanda Thomas
      August 30th

      Hi David,
       
      I hope I’m understanding your intentions, but I will preface this by saying that I’m more of an INDEX/MATCH person than a VLOOKUP person. Have you tried using MATCH to find where the column of where the word “Month” is found in row 4. That will provide you with the column number to which you can add 1 to (to go 1 column to the right) and use INDEX to pull from that location? (It’s actually sort of a reverse of how I usually employ an INDEX/MATCH formula).
       
      So, for instance, you’d start from a cell in row 4, like A4, for instance: (Please note that the quotation marks might need to be manually entered/replaced in the formula if you copy and paste from this comment since the comments are converting the quotation marks to a slight different type of quotation mark that Excel might not recognize)
      1. Use a MATCH formula in the row of data that you’re looking for the word “Month” in (you’ll probably need to use an asterisk if there’s any other data in the cells like the month name or number)
      =MATCH(“*month*”,$A4:$Z4,0)
       
      2. As an added precaution, you probably want to make sure that you don’t get any error messages if the word “Month” doesn’t occur in that row, so I’d recommend adding a conditional formula to handle the error (in this case, an IF and ISERROR formula)
      =IF(ISERROR(MATCH(“*month*”,$A4:$Z4,0)),””,MATCH(“*month*”,$A4:$Z4,0))
       
      3. As you can see, that just gives you the column number (which is your variable). With that, you can reference another cell that is however many columns to the left or right (or rows up or down) if you use the INDEX formula like so:
      =INDEX($A$1:$Z$100,ROW($B4)+4,IF(ISERROR(MATCH(“*month*”,$A4:$Z4,0)),””,MATCH(“*month*”,$A4:$Z4,0))+1)
       
      What you’re doing here is listing your entire range of data ($A$1:$Z$100 – it’s important to start from A1 as the cell references are employing the ROW function for comparison). From there, you’re going to go 4 rows down from your current row ($B4 is row 4, so 4 rows below will be referencing row 8). However, it is the MATCH formulas that are looking for the cell containing “month” in it and returning the column number (in the range of columns A through Z in row 4 – $A4:$Z4). However, if you want to reference the data that is also one column more to the right, you’d have to append that last little +1 at the end of the formula which adds 1 (as you can tell) to the column number that was found using the MATCH formulas.
       
      Speaking of +1s, I wouldn’t be doing my job if I didn’t remind everyone reading this post that if anyone likes this post or finds it useful, please share some love and maybe even (pretty please?) +1 it? Facebook likes and Tweets are appreciated as well (as you would expect from an internet marketing company). And if you’re wondering where to find them, the sharing buttons are at the bottom of the post before you reach the comments.
       
      Thanks for letting me make my plug in your comment David. I hope you don’t mind me hijacking the comment for that last minute plug and I certainly hope this solution helps.

  51. Aaron Gibb
    August 29th

    hi i was wondering if there is any way of getting an average from 7 cells in a row but only add up the cells with a value and only devide by the cells with a value?

    any help would be great

    thanks

  52. Deane Carter
    August 30th

    Hi Amanda,
    firstly love the blog and all the comments clearly show how great you are as a blogger helping those of us who are less excel brained.
    Bit of a tricky spot I’ve gotten myself int.o i have created for myself a master list of 2 teams , I have 2 named tables of values 1 column wide each,( 1 for each team Ravens and Crows.) What I am trying to program is next to a master list of all names a function that tells me if player 1’s name is in either list 1 or list 2 so that i can at a glance tell who hasn’t been allocated a team i just cant find a simple if list contains this value operation
    can you suggest something?

    so far i have the following formula
    =IF(OR(Ravens=I4,Crows=I4), “Yes”,”No”)
    it’s fairly straight forward I4 is the cell containing the players name, this value changes as i go down the list and crows and ravens are the names of the tables

    Thanks for any help you can provide
    A grateful engineering student

    • Amanda Thomas
      August 31st

      Hi Deane,
       
      Thanks for visiting, and just for the record, I was once a complete noob when it came to Excel so I understand where you’re coming from, and I aspire to learn more because there are some serious Excel Experts – particularly on the forums – that are masters in the art of Excel. I am but a padowan, but this padowan hopes that this formula would help do the trick:
       
      (Assuming that the name that you’re trying to lookup is found in cell A1)
       
      =IF(NOT(ISERROR(INDEX(Ravens[Column1],MATCH(A1,Ravens[Column1],0),1))),”Ravens”,IF(NOT(ISERROR(INDEX(Crows[Column1],MATCH(A1,Crows[Column1],0),1))),”Crows”,”Neither”))
       
      Please let me know if that works for you and kudos for studying engineering because that’s not an easy field, especially for the less engineering brained individuals such as myself :)

      • Deane Carter
        August 31st

        Thanks Amanda it works spectacularly had only to tweak a cell reference and it works better than what i even hoped for.
        Thanks again
        Deane

  53. Hayden
    August 30th

    Hi Amanda

    I REALLY hope you can help me with what I’m sure is something easy but i cant seem to get it to work..
    I have created a spread sheet that has YES and NO check boxes..
    If a NO box is checked, I have used an IF function to give me a numerical result.. I now want to ADD all those numerical results and get a final result in a cell at the bottom of that column…

    The regular SUM function doesn’t seem to work..

    Please help..

    Thanks

    Hayden

    • Amanda Thomas
      August 31st

      Hi Hayden,
       
      Are the numercial results that come from the IF formulas you’ve created the values that you’re trying to add? And the SUM function isn’t working either, right? Have you tried modifying your IF formula to wrap your numerical value in a VALUE() formula to see if it is an issue with numbers being viewed as text? e.g:
       
      =IF(D1=TRUE,VALUE(A1),IF(D1=FALSE,VALUE(B1),”N/A”))
       
      Do you know if there are any errors in the list? If there are any errors in the list that might explain why the SUM function isn’t working. Please keep me posted on how you were able to fix it in case it can help others who are reading this post.

  54. RP
    August 30th

    HI. Creating a data entry form. I would like all the cells to be shadded a certain color. When data is entered into that cell, it unshades itself. When the form is filled out completely, there are no shaded cells. Can you help me to do that?

    • Amanda Thomas
      August 31st

      Hi Rachel,
       
      Have you tried using the Conditional Formatting rule to format blanks? Here’s how you’d do that:
       
      1. Select the cells that you want this to apply to
       
      2. Go to the Conditional Formatting drop down menu (from the Home tab)
       
      3. Select “New Rule” (you’ll be given a window that gives you an option to “Format only cells that contain”)
       
      4. Click on the “Format only cells that contain” option which will provide you with a few other drop down menus
       
      5. Go to the leftmost drop down menu (right under the label “Format only cells with”) and select “Blanks”
       
      6. Hit the Format button and choose the formatting you want to use (for shading, you can select a grey fill color for example).
       
      7. Hit OK to save the formatting you’ve just designated
       
      8. Then hit OK again to save your new formatting rule
       
      If you need to go back and change this rule (or which cells it applies to, you can do that by going back to the Conditional Formatting drop down menu > Manage Rules (if you don’t see your rule listed, use the drop down menu at the top and select “This Worksheet” – this will provide you a list of all of your conditional formatting rules in your worksheet).
       
      If you need to remove the formatting from certain cells, you can either adjust the area in the “Applies to” field or you can highlight the cells you want to remove the conditional formatting for, then select Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
       
      If you want to copy the conditional formatting to other cells, you can also accomplish that by copying a cell that contains the conditional formatting then selecting the cell you want to copy the formatting over to, then using Paste Special > Formats.
       
      I hope that helps!

      • RP
        August 31st

        Hi Amanda,

        You’re awesome! I was playing around with Conditional Formatting, but just couldn’t figure it out. Thanks so much for the quick response. I really appreciate!

        Rachel

  55. Allen
    October 1st

    Excellent infomation, I managed to sort out my issue by merging 2-3 of the conditions and voila!

    many thanks

  56. Sheethal Chandra
    December 12th

    If the cell value is between 1-5, then it should come 1-5 days
    If the cell value is between 6-10, then it should come 6-10 days
    If the cell value is between 11-20, then it should come 11-20 days
    If the cell value is between 21-30, then it should come 21-30 days
    If the cell value is greater than 30 then it should come “greater than 30 days”

    Can you please help me put in resolving this?? Which formula we have to use?? Please..

  57. el
    December 15th

    Hi there,

    I’ve been searching desperately for a way to do something on Excel that I would have thought *must* be possible using IF in some way, but I can’t find out how. I was wondering if you could help me.

    1. I would like the cell to be shaded a certain color when data is entered into that cell is below and above tolerance/limit.

    2. When all the data is within the tolerance/limit it will remark pass or fail in the next cell.

  58. Dime
    December 17th

    Hi there, i need help cos am stuck…

    I need formula VLOOKUP or else which will do the job…

    Example…

    in D, show sum of values in C if A is same code as B…

    Thanks in advance

  59. Munir
    December 22nd

    It is really useful and learn able.

Leave a Reply

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