Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Learn Excel IF Formula – 5 Tricks you Didnt know

Posted on June 9th, 2008 in Featured , Learn Excel - 181 comments

using excel if formulaExcel has various functions, including functions to calculate inverse cosine of a given value, to multiply 2 matrices, to estimate the internal rate of return. But, most of us(well, just me then..) use just about 5-6 formulas to do our jobs. And IF formulas are a majority of these, so it doesn’t harm to learn a few interesting things you can do with just the excel IF functions.

1. Sum alternative rows / columns:

There comes a time when you are slapped with a sheet of data and need to sum every other row in it (dont ask me why, it happens, for eg. when you copy paste your credit card statement in excel), Ofcourse, we can always type the sum function with all those arguments, but we would rather chomp on that donut while excel does the dirty work for us. Thats why it helps to know that you can sum alternative rows / columns of data using sumif() formula [syntax and examples]. Sumif function sums a range of data that meets a specific criteria. In our case the criteria will be, “if the data element is in odd number rows”.

howto-sum-alternative-rows-columns-excel-using-sumif

All you need to do is add an additional column at the end of the table and fill it with 1s and 0s. (just enter 1 and 0 in 2 rows, select both of them and drag till the end of the table). Now we can use this column to test our condition by writing the sumif function as =sumif(condition range, 1, sum range) [learn how you can highlight alternative rows / columns in an excel table]

2. Count how many times each item on list A is in List B vice versa

Often when you are working on data spread across multiple sheets, it helps to know how many times each item on one list is repeated in another list(s). This can be done easily using a good old countif function. Excel countif formula counts data that meets a specific criteria. [syntax and examples]

learn how to using-countif-ms-excel-function

In the above example, I have used countif function to findout how many customers are there in each city (where customer data is in List B and city data is in List A). The formula looks like =countif(condition rage, condition), eg. countif($g$32:$g$47,"chicago") would tell us how many customers are in Chicago.

3. Quickly Summarize Data with countif / sumif:

Now that we have figured out how to use sumif and countif, you can use these two functions to create quick summary of your data.

For example, we can findout, average sales per customer per city dividing total sales data per each city (obtained using a sumif) with total customers in that city (obtained using a countif) as shown below. This can be a quick way to do pivot analysis of data without actually using excel pivot tables (very useful if you are allergic to excel pivot tables or not very happy using and constantly updating them)

data analysis with countif sumif spreadsheet functions

4. Lookup second, third … nth occurrence of an item in a list in excel:

Often we work with data that has lot of duplicates, for eg. customer phone number data that has grown over a period of time with new numbers added at the bottom of the list. Getting second, third, fourth or nth occurrence from the list can be tricky, by using a combination of countif and vlookup we can lookup nth occurrence from a list. [VLOOKUP tutorial and examples]

First in our data list we will insert another column and place the formula =current-item&countif(range till that point, item), this will append the number of smiths till that point to the end of smith, thus first smith would become smith1, second smith would become smith2, so on…

4-find-second-third-nth-occurrence-using-vlookup

Next, when looking up smith instead of using the initial column of customer data we will use our modified customer data to fire the lookup, for eg. vlookup("smith3", lookup range, 2, false) would tell us the 3rd phone number of smith. Note the last argument to the vlookup as “false”, since our list may not be alphabetically sorted, we have to use “false” to force excel to keep looking till it finds the 3rd smith row.

5. Reduce your nested if()s to one function

Do you know that you cannot next excel if functions beyond 7 levels? Thankfully, most of us never go beyond 3 or 4 levels. But why write even that many levels when you can use choose() function, which is like a switch structure in programming. A typical switch function would look like, =switch(condition, outcome1, outcome2, outcome3...), for eg, =switch(3,"good","average","poor") would return “poor” when used. The only limitation to the switch() excel function is that it accepts numbers for testing the condition. But you can overcome this with some creativity, like I have shown in the below student letter grading example:

5-using-excel-choose-function

How did I convert the letter grade to a numeric in the choose(), well thats for you to figure out :)

What is your favorite IF formula trick?

Well, that is for you to tell me. What is your favorite Excel IF function tip / usage? Share it in the comments, let everyone know

Download IF Formula Examples and Play with them

I have prepared an excel sheet with all these if function examples, Click here to download it and experiment.

Learn More:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

181 Responses to “Learn Excel IF Formula – 5 Tricks you Didnt know”

  1. John Satta says:

    Just wanted to say I love your excel tips – always thought I was an excel guru, but my hat is off to you!

    Wish I could think of a good IF tip so I could contribute :-(

    Great stuff – keep it up!
    -John

  2. Dissappointed M&A part III says:

    Buddy,

    - Thought this would be the best place to catch you as this is the only place i can find that u still come. Did you get a chance to go through the “viral note” i had send you. Well the note was prepared when i was down with viral fever and had nothing else to do.

    But my excel demi god i didn’t realise you would eat it up with your ever increasing hunger for useless stuff and discharge it the next day. No response to my sms, or any of the messenger msgs.

    Waiting for your blessings like a melancholic lover for his maiden to return

  3. Keith Dsouza says:

    Nice tips, most of them will be quite useful to me :)

  4. Chandoo says:

    @John Satta … that is so kind of you, thanks for your comments.. :)

    @Keith … Welcome to PHD, thanks :)

    @M&A … hehe, responded.. stop being cryptic and come out in public :D

  5. [...] Excel Tips: How you can copy validation criteria from one cell to another, Master IF functions in Excel – 5 tips, 15 fun things you can do with excel now Tags: data validation, Excel Tips, how to, learn, [...]

  6. Anelisa says:

    I would like to determine a catergory by selecting the first two digits from a figure e.g. from 011000, 020000 – if it’s 01 then I’ll write Educator in another collumn next to the figure, 02 = Practitioner.. etc

  7. Chandoo says:

    @Anelisa: Hope you have already figured this out, otherwise here is how you can do this:

    =if(left(figure,2)=”01″,”Educator”,if(left(figure,2)=”10″,”Practitioner”…

    let me know if this doesnt help :)

    • arvind says:

      For a Reservoir I have 10 conditions for storages (million cubic meter) corresponding to elevations (meter) of Reservoir. The storages at corresponding elevations are as below

      Elevation 
      Storage Capacity 

      225.00
      1.27

      230.00
      6.55

      235.00
      17.88

      240.00
      36.86

      245.00
      64.61

      250.00
      101.49

      252.00
      149.47

      252.25
      122.01

      254.00
      139.64

      256.00
      161.40

      I have monthwise storages and I would like to use one single formula for elevation corresponding to the value of storage (The intermediate values are to be interpolated). The monthwise storages are as follow-

      Month
      At the end of the month
      Remarks

      Storage 
      Elevation

      1
      2
      3
      4

      June
      161.4
      256.00
      The FRL of the reservoir is 256.00 meter 

      July
      161.4
      256.00

      August
      161.4
      256.00

      September
      161.4
      256.00

      October
      116.14839
      251.63

      November
      47.052251
      241.84

      December
      2.7023792
      226.36

      January
      0
      0.00

      February to May
      0
      0.00

      In column No.3 of Elevation I used if formula and got results. Formula used is =IF(O8=161.4,256,IF(O8>139.64,254+((O8-139.64)/(161.4-139.64))*2,IF(O8>122.01,252.25+((O8-122.01)/(139.64-122.01))*1.75,IF(O8>119.47,252+((O8-119.47)/(122.01-119.47))*0.25,IF(O8>101.49,250+((O8-101.49)/(119.47-101.49))*2,IF(O8>64.61,245+((O8-64.61)/(101.49-64.61))*5,IF(O8>36.86,240+((O8-36.86)/(64.61-36.86))*5,IF(O8>17.88,235+((O8-17.88)/(36.86-17.88))*5,IF(O8>6.55,230+((O8-6.55)/(17.88-6.55))*5,IF(O8>1.27,225+((O8-1.27)/(6.55-1.27))*5,0))))))))))
      But this If formula is very lengthy can anybody suggest me a shortcut formula.
      –arvind

  8. [...] this? Also read Master copy – paste with these 17 paste tricks, 6 things you dont know about excel if() functions, 15 fun things you can do with excel Tags: conditional formatting, date and time, Excel [...]

  9. [...] we get raw textual data from various sources and we need it to be sorted. While fooling with the COUNTIF() formula, I have realized a powerful yet little known feature that can be exploited to sort text using [...]

  10. [...] Using COUNTIF() to replace pivot tables: We all know that you can use countif() to replace pivot tables for simple data summarization. For eg. if you have customer data in a table and you would like to know how many customers you have in each city you can use countif() to find that. countif(“master-data-range”,”city name”) More on this method of using countif and 4 other ways of using excel if () formulas [...]

  11. guest says:

    Nested if’s:
    Nice, but still suffering from the limitations of choose (29 param’s max)!
    Why not using index:
    =INDEX(grades;CODE(D99)-64;)
    where grades is the matrix with the grades?,
    drefsa

  12. Ketan says:

    @ guest :
    You may use the offset command too
    =offset(grades;CODE(D99)-64;)
    Where grades is name of cell below which all the required grades written for A,B,C,D,F….

  13. guest says:

    @ ketan,
    sure, works as well.
    My inital comments was meant to be as close to the logic of the examples given as possible.
    generally I cannot befriend myself with more than 3 nested ifs. better to switch to index, offset, or vlookup solutions for numerous reasons.
    But I have question though:
    the code() function uses the ANSI – set on windows. What about other systems like MAC? Is it returning the same results? Sorry-but I am lacking a MAC…
    drefsa

  14. Chandoo says:

    @Guest: Welcome to PHD and thanks for sharing your ideas. I like the index, lookup based solutions compared to normals ones. But as with any solution these are also good for a particular context – in this case, large enough data.

    Btw, CODE() returns ASCII code of the character in the input and it has got nothing to do with the computer you are using. ASCII is a standard code across all computers and used to represent various characters with numeric (actually hex) values for internal representation.

  15. guest says:

    Chandoo,
    you are right, a solution always depend on the given problem.
    One thing puzzles me:
    in the German XL-help it says for Code(): WIndows Ansi, MAC Mac charset.
    If the numbering is of the characters is the same all will work fine of course. Just wondering.
    drefsa

  16. Chandoo says:

    @Guest: I am sorry for misleading you. You are right. CODE() returns ANSI or Mac Character codes based on the system you use.

    But thankfully, they return value would be same for most of the day to day characters and is equal to ASCII code.

    There are quite a few standards when it comes to internally representing characters in 8 bits – like ANSI, ASCII, MacRoman Set, ISO-8859-1 etc.

    See this for more: http://www.alanwood.net/demos/charsetdiffs.html#f

    but rest assured as for most values code() would return same output no matter what OS you are using.

  17. guest says:

    chandoo,
    thx! that’s a useful link.
    I’ll check out the “non-conformities” eventually. In my opinion it is just iseful to be really clear on the potential pitfalls of a method. On the hand: XL sometimes reminds me of a inexhaustible bag of surprises ;-))
    btw: like ur blog
    drefsa

  18. [...] What the IF? – learn 6 cool things you can do with excel if() functions [...]

  19. CraigM says:

    Chandoo thanks for this.

    I have a fairly large spreadsheet that this has helped greatly speed my work up.

    There are a couple of things that would make it complete. I have about 7 different items that I would like the high, low and median prices for. Is their an easy way, not unlike this, that will extract them from an unsorted list?

    Thanks

    Craig

  20. Satish says:

    Dear Chandu,

    I like you simple and direct examples, I am still mid-way, I liked you example No 3 in this Page( 6 Cool things you can do with Excel if() )

    Here In this Example I like to add one more Column K with the Data “New” and “Old”, Maybe Column Heading will be “Cust Type”.

    Now I need to have the Total Sales in Column G, with only “New” Customer of that City. How can I make this Condition,

    Pls note , I don’t want to Filter the “Cust Type” /Column K
    (I also think in the Range Filter will not work).

    regards
    Satish K
    Mysore

  21. Chandoo says:

    @CraigM: I hope you got your answer. Sorry for the delay in response. Whether you have 7 items or 70 items, using max(), min() and average() functions on the range will extract the maximum, minimum and average values for you. If you need median value, try the median() function. Since all these functions accept ranges as inputs, they are very easy to use and extend to tables.

    @Satish: Welcome to PHD Blog. Thanks for asking your question.

    Unfortunately sumif() in excel only accepts one condition range. That means we need some tweaks to get new customer sales per city. Here is how I would do it.

    Add another column named type-city and use a formula like this to fill that column: = customer-type & “-” & city
    so it will have values like New-Columbus, Old-Washington etc.

    Now in the sumif() we will give this new column as condition range and for the condition value we just add “new-” before the city name we want the sum for. For eg. =sumif(type-city-list, “new-columbus”, sum-values) will tell us how much sales came from new customers in columbus city from the list.

    Let me know if you have some difficulty implementing this.

  22. Satish says:

    Thanks Chandoo
    for the Work around, solun I will try implement it .
    Happy Holiday

    regards
    Satish K

  23. [...] Show temperature using these thermometer charts, Get stock quotes to your workbook with one click, Master your ifs and buts – learn these 6 tips on if(), Project plan in 60 seconds, your time starts now!, Honey! I shrunk the bar charts and much [...]

  24. [...] Planning to create a gradebook or something using excel, you are bound to write some if() functions, but do you know that you can use choose() when you have more than 2 outcomes for a given condition? As you all know, if(condition, fetch this, or this) returns “fetch this” if the condition is TRUE or “or this” if the condition is FALSE. Learn more about spreadsheet if functions like countif, sumif etc. [...]

  25. Asad says:

    Thank a lot for this.I appreciate your work..
    God Bless You..!

  26. GhOsT says:

    I have a problem and i hope you can help me. I need to create a if statement that will give me results. It is points for horse riding if the rider gets a 1ste place he must get 100 points and if he get 2de place he must get 95 points and so on. Can any one help me. Please E-mail me at leonsaunders20@gmail.com

  27. Chandoo says:

    @Ghost : you can simply write a formula like =(21-a1)*5 assuming a1 has the place.

  28. Andrea says:

    I have a whole stack of weighted averages that roll up to a series of overall tasks. I have figured out how to get the sum of the weighted averages – however I cannnot figure out how to get percentages for the top tier tasks…

    An example to help explain:
    1) Build the house
    a) Foundation
    i) Excavate the trench (weighting 30%) 36% complete
    ii) formwork (weighting 25%) 25% complete
    iii) rebar (weighting 15%) 17% complete
    iv) pour concrete (weighting 30%) 0% complete

    I know how to get the weighted average for the first level up (i.e. the foundation), but I am struggling to get the progress complete for the top level (i.e. Building the House) when I have a whole lot of groups such as the first level.

    I hope I have clarified what I am asking for, and can you help??

  29. SeanL says:

    Is there a straightforward way of extracting the nth biggest value from an unsorted list?

    I tried large(A$:A$,n) but that gave me the nth value for the unsorted list (which would be perfect if my data was sorted, rather than unsorted).

  30. Chandoo says:

    @Andrea… I am sorry, I have noticed this comment a bit too late. Are you still looking for a solution?

    @SeanL: the large formula should work in your case. Can you post the data for us to see ?

  31. SeanL says:

    Chandoo:
    As it turns out, I should have been using small, not large, and the source of the problem is that I was wanting to apply the formulae specifially:
    =SMALL(DataRange, TRUNC((COUNT(DataRange)*N$1)-(1.96*SQRT(COUNT(DataRange)*N$1*(1-N$1)))))
    =SMALL(DataRange, TRUNC((COUNT(DataRange)*N$1)+(1.96*SQRT(COUNT(DataRange)*N$1*(1-N$1)))))

    To a column on a table with a filter applied to it, and have the forumla ignore the hidden values, and only perform the operation on the filtered values – that way I don’t have to mess around with redefining the data range if I want to look at a slightly different data set (eg a different date range), or defining data ranges for several analytes across several sites.

  32. scout says:

    I have a list where some people have paid and others not. I want to create a sheet that has only the people with “0″ dollars balance. Columns I am choosing from are name, rank, balance. These are unsorted (order cannot be changed). I would like the “Balance Due” sheet to choose which person has a non-0 balance and list them in another sheet with their name, rank, and balance.

    I know I need to use a IF statement with the balance column 0 (not equal to 0), but I can’t figure exactly how to return the corresponding name and rank along with the balance. I am thinking I will need to nest a INDEX or MATCH in there somehow.

    Thanks in advance for speedy reply!

  33. Sessoms says:

    Here is my interesting IF statement question…

    When using a long SUMPRODUCT as part of the IF statement condition, is there a way to avoid having to rewrite the SUMPRODUCT statement as one of the Values?

    EX. IF(SUMPRODUCT(blah, blah)<100, "No Data", SUMPRODUCT(blah, blah))

    I typically do arrays like this and the formulas get awfully difficult to read.
    I wish I only had to write SUMPRODUCT statement one time and not repeat it.

    Any ideas would be much appreciated! Thanks! I love your site!

  34. Chandoo says:

    @Scout: I am sorry, but I couldnt get back to you any earlier. Did you find the answer? If not, let me know, I can help.

    @Sessoms: Do you have Excel 2007? if so you can use IFERROR formula. Otherwise, the cheapest way is to find the parts of the formula that cause an error and just check for them in in the first part of IF. Another option and probably a cleaner one is to use a helper column. You can easily hide it or place it on a hidden sheet if you dont want your audience to see it.

  35. Sessoms says:

    Thanks Chandoo. I did see the IFERROR formula and saw how it could be useful when I use those times I used IF(ISERROR(formula_goes_here), “”, formula_goes_here)

    Unfortunately, when the condition is not to test for an error, I didn’t find anything that fit the bill. For clarity of the formula, I agree, the helper columns are an easy solution and one I will probably resort to in the future.

    Thanks for taking the time!

  36. Chandoo says:

    @Sessoms, if it is just a display thing, you can also use custom cell formatting with a code like this [<100]“No data”;0;

    This will just show “No data” when the value is less than 100, but the value is still there in the cell. So if you pass the range to a chart or another formula they still get values less than 100.

    Learn more about custom cell formats here: http://chandoo.org/wp/tag/custom-cell-formatting/

  37. Mihai says:

    Thanks for choose function tip. Very useful. Nice site for everyone trying to create within excel. Success…:)

  38. Karan says:

    Thnx Chandoo.. thz ws vry useful….. thnx a lot…!!!!!!! :)

  39. naveen says:

    can u tell how to use if and date functions……

    Ex if the payment made in month of april , tds has to be deducted on this date of may, like this

    which will may have 12 conditions. for each month…

    • MURALI says:

      You don,t require if & 12 conditions to use. More simply you can do this task as below:
      1. you have 2 cols as Date paid, TDS due Date .
      2. Fill Date paid. Say this is 25-04-2014.
      3. Say, you want the TDS due Date to be on 5th of next month. Use this formula in this col =EOMONTH(A2-1,1)+5. This will give result as 05-05-2014.
      4. when you fill date as 15-10-2014, you will get the TDS due Date as 05-11-2014.
      Hope this suffices/ meets your requirements.

  40. [...] go ahead and use IF formula, if that is what you need to [...]

  41. [...] to use the intermediate level functions within Excel. These include: sum, sumif, macro’s, logic (if, then, else), auto sum, filtering, auto subtotal, sorting, charting, pivot tables, auto formatting, conditional [...]

  42. m.mahmood says:

    I need some basic formulas of
    excl but I cld find any thing ….. ok its my lock
    if any of u now the basic formulas then plz mahmood4144gmailcom

  43. Dave S says:

    Want to use the countif for a column containing two items, for example, “PRT-XXX” and “Stock”. The “Stock” stays the same, but the “PRT” changes each time PRT-001, PRT-002.
    I tried =countif(a1:15,”prt”), but doesn’t work. Thanks.

  44. Hui... says:

    @Dave
    =SUMPRODUCT(–(LEFT(A2:A100,3)=”PRT”))
    This isn’t case sensitive

  45. Chandoo says:

    @Dave.. also try =COUNTIF(A1:A15,”PRT*”) to count values that begin with PRT

  46. Dave S says:

    Thanks Hui & Chandoo for all your help!

  47. W says:

    Im trying to find a fomula that does something like
    if master,s4=yes a3=master,a4
    Im sure its easy but im having a issue with it.
    Many thanks
    W

  48. shayan says:

    i want to make a formula for school grading in excel
    for A+ grade 90% to 100%
    for A grade 80% to 89%
    for B+ grade 70% to 79%
    for B grade 60% to 69%
    for C+ grade 55% to 59%
    for C grade 50% to 54%
    & for F grade 0% to 49%

    is there any formula available for that please tell me.
    i m very thankful for …

  49. Hui... says:

    @Shayan
    Make up a Table in say A1:B7 as below

    0 F
    0.5 C
    0.55 C+
    0.6 B
    0.7 B+
    0.8 A
    0.9 A+

    Then use a VLookup to retrieve your grades:
    =VLOOKUP(E1,A1:B7,2,TRUE)

    Where E1 has the score

  50. shayan says:

    =IF(I12>=90,”A+”,IF(I12=”80″<"89","A",IF(I12="70"<"79","B+",IF(I12="60"<"69","B",IF(I12="55"<"59","C+",IF(I12="50"<"54","C","F")))))) i use that formula but its not working it shows only F grade

  51. shayan says:

    i dont get ur meaning hui,
    if i write these it should be in A column why b7 range u give in formula
    0 F
    0.5 C
    0.55 C+
    0.6 B
    0.7 B+
    0.8 A
    0.9 A+
    how do i wrile i wrote it in column A (a1 to a7)
    please hui tell me please
    i used that formula it shows #N/A

  52. shayan says:

    thanks thanks thanks its working great hui thanks a lot

  53. Hui... says:

    @Shayan
    I’m glad you worked out that there is 2 columns of data
    .
    I recommend you use this technique as it is easy to scale up to add more grade ranges and use else where is your worksheet
    .
    FYI the If statement should be
    =IF(I12>=90,”A+”,IF(I12>=80,”A”,IF(I12>=70,”B+”,IF(I12>=55, “C+”,IF(I12>=50,”C”,”F”)))))
    .
    If you copy/paste the If Statement above, retype all the ” ‘s

  54. shayan says:

    I don’t believe that you are a genius in excel i had applied these kind of formulas much times but i it was not working, how you find that errors to retype all commas its working.
    how do you learn these things, i’m impressed…..

    Thanks for all of your support Hui

  55. Hui... says:

    @Shayan
    You have to be careful copying and pasting things from web sites
    A lot of time the formula with ” ‘s in it is the wrong character
    Retype all the characters that look like ” with ” manually fixes the problems
    There may also be other non A-Z or )-9 characters where a similar thing occurs

  56. Chris says:

    I’m trying to create an IF function that will allow me to take a column of values and create a condition where if the value of a cell in column A = a value found in a cell ANYWHERE in column B, it will generate a particular response.

    For example, I want to generate a response “ABC” in the following:

    Column A Column B (Desired Response)
    750 600 FALSE
    100 50 ABC
    200 100 ABC
    300 200 FALSE
    400 900 FALSE
    700
    1000

    I cannot figure out what the formula would be. Any ideas as to how to create this?

  57. Hui... says:

    @Chris
    I’ve assumed your data is in A2:B8
    If so try this
    =IF(OR(B2="",ISERROR(FIND(B2,$A$2 & $A$3 & $A$4 & $A$5 & $A$6 & $A$7 & $A$8 ,1))), "False", "ABC")
    If you have hundreds of cells in Column A
    Look at using a ConCat formula to concatenate all the cells first instead of manually adding them together
    Refer: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
    .
    If you do this you can then use a formula like:
    =IF(OR(B2="",ISERROR(FIND(B2, Concat(A2:A1000) ,1))), "False", "ABC")

  58. Yinkus says:

    Hi Guys,

    I am having problem with the following formular: =IF(AND(B22=Yes,(C22=Yes,(D22=N0,(E22=No,(F22=No,”Go to step 2- sample selection”,”Apply Silo”))))))

    Please I need help from you guys to fix this

    • Hui... says:

      @Yinkus
      Try:
      =IF(B22="Yes",IF(C22="Yes",IF(D22="N0",IF(E22="No",IF(F22="No","Go to step 2- sample selection","Apply Silo")))))
      or
      =IF(AND(B22="Yes",C22="Yes",D22="N0",E22="No",F22="No"),"Go to step 2- sample selection","Apply Silo")

      Your text values Yes and No must have ” ‘s around them
      also Note all the If you were missing
      I hope this is correct as I wasn’t sure about your logic

  59. Yinkus says:

    Hi Hui..
    Many thanks for your guide. I have applied your suggestion but the formular is returning ‘True’ or ‘False’. What I actually want to achieve is to have ‘go to step 2 – sample selection’ or ‘apply silo’ as my return. How can I achieve this?

  60. Yinkus says:

    These are the values:
    B22=Yes,C22=Yes, D22=No,E22=No,F22=No and the formula is as follows:
    =IF(AND($B22=”Yes”,$C22=”Yes”,$D22=”N0″,$E22=”No”,$F22=”No”),(“Go to step 2 – sample selection”),(“IFRS 12 is applicable”))

    Thanks

  61. Hui says:

    Yinkus
    If you copied my equation above make sure that you retype the ” characters as sometimes they get replaced by a similar looking character
    But this works fine
    =IF(AND($B22=”Yes”,$C22=”Yes”,$D22=”No”,$E22=”No”,$F22=”No”),”Go to step 2 – sample selection”,”IFRS 12 is applicable”)

  62. Yinkus says:

    Hui,
    Many thanks for your help. You have really granted me a great relief.
    Meanwhile, there is this

  63. Yinkus says:

    Hui,
    Many thanks for your help. You have really granted me a great relief.

  64. shayan says:

    he is a genius in excel.
    master minddddddddd

  65. Yinkus says:

    Hi Hui,
    I ve got another problem. I am trying to write formula for the following:
    Logic 1 IF B14=Y and D14=N
    or
    Logic2 IF B14=No,G14=Y or H14=Y or I14=Y or L14=Y and K14=Y
    or
    Logic3 -If N14=Y or P14=Y

    This problem involve so many cells and it is either logic 1 or logic2 or logic 3 as stated above.

    Please help out.

    Kind regards

    Then it is silo, otherwise, not silo

  66. Hui... says:

    @Yinkus
    Give this a go:
    =IF(OR(AND(B14="Y", D14="N"), OR( AND(B14="No", G14="Y"), H14="Y" ,I14="Y", AND(L14="Y", K14="Y")), OR(N14="Y", P14="Y")), TRUE, FALSE)

  67. Yinkus says:

    Hi Hui,
    Many thanks for your help. I have tried the formula but its returning false.
    What I actually intend to achieve is if B14=”Y”, D14=”N”, it should return ‘True’ notwithstanding what is in other cells. However, if B14=”N”, either of these must occur : G14=”Y”) or H14=”Y” or I14=”Y” or L14=”Y”and K14=”Y”,for it to return ‘True’. Otherwise (N14=”Y”, P14=”Y”must occur for it to return ‘True’.
    Please help out

  68. Hui... says:

    so change the equation to suit your logic
    =IF(OR( OR(B14=”Y”, D14=”N”), OR( AND(B14=”N”, G14=”Y”), H14=”Y” ,I14=”Y”, AND(L14=”Y”, K14=”Y”)), OR(N14=”Y”, P14=”Y”)), TRUE, FALSE)

  69. Yinkus says:

    Hi Hui,
    I have tried this but I am having ‘#Name’ error

  70. Yinkus says:

    Hi Hui,
    Thanks so much for your help. I have fix the error and the formula is working fine now.

    Once again, many thanks for always being there for us.

  71. Hui... says:

    @Yinkus
    What was the solution ?

  72. Sriram says:

    Dear All,

    i have the following situation and need your help

    I want to use IF THEN ELSE Function or any other Solver in the following situation .

    1- I have a (Master sheet )customer codes in colom A , Customer name in Colom B and data in the corrosponding colom.

    2- Every month i get a new Monthly sheet with the same (A &B ) Colom (not necessarily in same order as in Master sheet ) and new data values as per their Purchses this last month.

    3- My bojective is to

    i – To Copy data from new monthly sheet to the corrosponding customer in the master sheet .

    4- I tried the following .

    i – =IF(CustomercodeMastersheet =Vlookup(Customercodenewmonth sheet )) then copy “NewmonthsheetC to Master sheet colom C” ELSE “0″) but it didnt work.

    Am new to this forum and need Expert help . Am not so much familiar with excel to put it in proper Technical language .

    Thank you in anticipation.

  73. Hui... says:

    @Sriram
    Looks like you should be using and Index/Match combination
    Have a read of http://www.contextures.com/xlfunctions03.html#IndexMatch

  74. sam says:

    I have a huge problem i have data which represnts in minutes and seconds from a survey so it apperars wrong i use tranpose to change and the thris clounm doesnt let me conncat ante i have to draw a guage with this data so i need the avaerage time taken to complete the task and when i look at trying to make the guages it is very hard can you please help with sugar on top thanks
    min sec min sec
    0 5 7
    1 5 2
    2 7 0
    3 11 1
    4 8 0
    5 10 1
    6 2 1
    7 1 1
    8 1 1
    9 0 1
    10 0 0
    11 0 1
    12 0 3
    13 0 0
    14 0 0
    15 0 2
    16 0 0
    17 0 1
    18 0 1
    19 0 0
    20 0 1
    21 0 0
    22 0 1
    23 0 1
    24 0 0
    25 0 0
    26 0 0
    27 0 1
    28 0 0
    29 0 1
    30 0 0
    31 0 4
    32 0 1
    33 0 0
    34 0 0
    35 0 0
    36 0 0
    37 0 1
    38 0 0
    39 0 1
    40 0 1
    41 0 0
    42 0 0
    43 0 0
    44 0 1
    45 0 2
    46 0 1
    47 0 0
    48 0 1
    49 0 1
    50 0 2
    51 0 1
    52 0 0
    53 0 0
    54 0 0
    55 0 2
    56 0 1
    57 0 0
    58 0 1
    59 0 1
    60 0 0

  75. sam says:

    Right I have entered data into a survey and the results come back in a less then helpful way I do believe that there is a way not only to find the average but a better way to present the data I would like it represented so it can be placed on a dashboard and so it has to be eye pleasing .the way the survey spits the data out is very hard to use through the medium of excel I will again show how it comes out and how can I reset the data to concatenate the minutes , and seconds in the third column and keep the numbers the same and then formulate a chart of the average time taken is there a string I could use or can I arrange the data a certain way , my boss would be pleased if I can make a graph out of this because he cannot , many thanks you guys I need help with this as I have wasted two days already ,,,,,helllllllppppppppppp.

    min min sec
    0 5 7
    1 5 2
    2 7 0
    3 11 1
    4 8 0
    5 10 1
    6 2 1
    7 1 1
    8 1 1
    9 0 1
    10 0 0
    11 0 1
    12 0 3
    13 0 0
    14 0 0
    15 0 2
    16 0 0
    17 0 1
    18 0 1
    19 0 0
    20 0 1
    21 0 0
    22 0 1
    23 0 1
    24 0 0
    25 0 0
    26 0 0
    27 0 1
    28 0 0
    29 0 1
    30 0 0
    31 0 4
    32 0 1
    33 0 0
    34 0 0
    35 0 0
    36 0 0
    37 0 1
    38 0 0
    39 0 1
    40 0 1
    41 0 0
    42 0 0
    43 0 0
    44 0 1
    45 0 2
    46 0 1
    47 0 0
    48 0 1
    49 0 1
    50 0 2
    51 0 1
    52 0 0
    53 0 0
    54 0 0
    55 0 2
    56 0 1
    57 0 0
    58 0 1
    59 0 1
    60 0 0

    So I need a third column showing the times so min sec I have tried this using the format and concatenate but it is giving me the wrong data , so how can I put it together and get a data to use for a lovely graph such as a gauge and put it on the dashboard.

  76. Hui... says:

    @Sam
    You have 4 headings and 3 columns of Data ?
    min sec min sec
    0 5 7

    Can you please clarify what is in each column ?
    and then you say “So I need a third column showing the times so min sec ”
    Please clarify this so we can help you

  77. Paul Shipgood says:

    I’m trying to arrange an “IF” “THEN” formula where each cell referred to can have a number between 1 and 26 (i.e. 26 possible answers) but note that I can only nest together up to seven IF/THEN statements. The following function would give me the result I required if there were only seven possible answers but is there any way to write the function to provide 26 different answers, please?

    =IF(B2=1,Q2,IF(B2=2,Q3,IF(B2=3,Q4, IF(B2=4,Q5, IF(B2=5,Q6, IF(B2=6,Q7, IF(B2=7,Q8, IF(B2=8,Q9,))))))))

  78. Hui... says:

    @Paul
    Try using Choose
    =Choose(Index Number, Value 1, Value 2, Value 3, …, Value n)
    in your example
    =Choose(B2,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10, … ,Q27)

  79. Paul Shipgood says:

    Brilliant, worked a treat. Many thanks.

  80. Leon says:

    After working through the spreadsheet, I saw the invite for anyone to submit their own IF formulas. Having only been on the site for a month, I was quite excited to be able have a submission which I put together after my first Excel School lesson (Nested IFs) and the SUMPRODUCT free lesson.

    So, my most used IF formula (now) is this:

    =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

    I use it to count client treatment activity for any given period. Clients will appear as a row of data for each treatment they have received during their visit:

    __|____A____|___ B____|______C____|__D_|
    01|__DATE___|_CLIENT#_|_TREATMENT_|_DC_|
    02|01/12/2011|_abc123__|_treatment1_|_1__|
    03|01/12/2011|_def456__|_treatment1_|_1__|
    04|01/12/2011|_abc123__|_treatment2_|_0__|
    05|01/12/2011|_abc123__|_treatment3_|_0__|
    06|07/12/2011|_abc123__|_treatment2_|_1__|

    (The above is just an illustration; I typically use this on a db extract of 8000 lines with 20-30 columns).

    The formula identifies the first instance of the client’s date of visit and unique number returning a ‘1’ in the ‘DC’ column (D2). All other entries for that day, by that client are ‘0’ until the criteria changes. And, It’s independent of sort order.

    The SUMPRODUCT part can be expanded to include more criteria, not just 2 as in my example. Just add a set of brackets for each one, like so:

    =IF(SUMPRODUCT((($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2)))>1,0,1)

    I’ve used this formula extensively for generating other helper columns as well and find it very versatile, especially with SUBTOTAL headed sheets and filters.

    Before finding this site, I knew very little about Excel 2007 (although I thought I knew more). I’m now discovering so very much, and understanding it, rather than just copying whatever I find on the net.

    I would love any feedback from readers.

  81. Krys says:

    Hi,

    Sorry I don’t have much to add in terms of tips, but I do have a query. I have a row of data which states if a student is full time or part and another row that has the amount awarded. Is there a way that I can work out how many full time students got awarded without cut and pasting my worksheet into a different document and sorting it? Many thanks in advance :)

  82. Liz says:

    I’m trying to use the IF function to have it determine if a cell matches one criteria, then if it falls within a certain month. (I’ll need one for every month of the year). I thought I had it set with the following, but it returned nothing for any month other than January (current).
    =IF(‘[Template - Master.xlsx]Master Data’!$P4=’[Template - Master.xlsx]Format – Don”t Move’!$E$2,IF(‘[Template - Master.xlsx]Master Data’!$E$3>’[Template - Master.xlsx]Format – Don”t Move’!$K$2,IF(‘[Template - Master.xlsx]Master Data’!$E$3A1,DATEVALUE(“7/30/2009″)<=A1),"July")

    and tried adapting my formula to that. After adapting to try to make it work, I have the following:
    =IF('[Template - Master.xlsx]Master Data'!$P3='[Template - Master.xlsx]Format – Don''t Move'!$E$2,IF(IF(AND(DATEVALUE("1/1/2012")=’[Template - Master.xlsx]Master Data’!$E3,’[Template - Master.xlsx]Master Data’!$E3,””),””),””))

    It isn’t returning anything but an error message, so I’m sure it is something very obvious that I’m just missing.

    Any suggestions? I’m fine using other functions; I’ve just been using variations on the If function throughout these and am trying to make it simple for anyone looking at it down the line.

    Also, great posts – very helpful. I’ve bookmarked a few to review again later.

  83. R N Moorthy says:

    I have a problem like three columns of marks first column internal marks , second column External and third Total. Now if any student gets less than 24 in Internal and also less than 40 in external he is fail other wise E, D C B AND A GRADES PL HELP

  84. Andy says:

    I am trying to get a formula written. So far i have:

    =IF(B2=”Single”,”Room 3″) This Part was easy enough.

    What i need is the room 3 to reference to a drop down box i have created. This way when single is selected on the spreadsheet i can choose which rooms i can use.

    Cheers,

    Andy

  85. Brian.Shanahan says:

    I am trying to write a formulae that in words is described like this:
    IF cell number L3 is yes then Cell F3 needs to be multiplied by 0.12

    Can anyone help me

  86. Leon-K says:

    Brian, If you still need help with your query, firstly, you haven’t stated if L3 is a boolean value or whether it is just a text string. Secondly, you haven’t stated whether you need this for 1 cell or many, pasting it down a column (Absolute or relative cell references?). Finally, you haven’t said what to do if L3 isn’t ‘yes’. So, I give you 2 suggestions based on what you have said as follows:

    1. Boolean value for L3 = Yes:

    =IF(L3,F3*0.12,NA())

    This works because the syntax of IF() is IF(Logical Test, [Value if true],[Value if false]). ‘Yes’ is a TRUE statement. If ‘Yes’ is not in the cell, a FALSE statement, #NA() appears in the cell instead.

    2. Text String for L3 = Yes:

    =IF(L3=”Yes”,F3*.12,NA())

    This merely looks for the word ‘Yes’ in L3 and calculates as required. If any other entry is present in L3, #NA() is displayed instead.

    HTH

    Leon-K

  87. Nilesh says:

    I’m trying to use the IF functiont& the formula is as below but for 0 i am not getting the correct answer, please help me as i want to use this formula reguraly

    =IF(J2>=H2,”1″,IF(J20,”S”,IF(J2=0,”N”)))

  88. John says:

    Can someone give me advice? I am a ham & egger with excel, doing basic stuff. Especially after seeing what you folks know. I am trying to create a formula to finish a spreadsheet and have no idea of how to write it. It needs to do:
    IF cell G6 is less than cell A2 multiply cell G6 x cell C2 IF cell G6 is more than Cell A2 Enter value in cell D2.

    • Chandoo says:

      @John… Thanks for your love and question.

      Please note that in Excel if a Cell contains a formula, you cannot use the same cell for user input. So in your case, the formula can be written in another cell (like D1) and D2 can be used for user input, like this:

      in D1 write =IF(G6 < A2, G6 * C2, D2)
      and in D2 user can enter any value they want and if G6 is less than A2, you will see that in D1.

  89. Michael says:

    I am trying to make my check register in excel transfer or copy debit amounts to other columns so that, at the end of the year, all expenses for “cost of goods”, supplies, communications, employee, etc etc.

    I am looking for a formula, that will read the text entry in the Description column, and if correct, then copy the debit amount from one column to another column in the same row.

    something like this.
    =if(c6=atlantic electric, then this cell equals d6)
    it should be simple but i have not been able to get any if, vlookup, hlookup, index, funtions to work.

    Can anybody help please.
    Michael

  90. Rahul says:

    Hi Chandu,

    Sorry i am new learner in excel… and not sure how this your below formula works.

    =CHOOSE(CODE(C2)=64,”EXCELLENT”,”GOOD”,”POOR”,”VERY POOR”,”BAD”)

    I am totaly confused on “64″ what is this.. is this a cell reference..

    Please help me Man…

  91. Nilesh says:

    i am using below formula & its not work 100% for zero its refecting short insted of No Stock kindly advice as i am using this formula reguralarly

    =IF(J11>=I11,”Available”, IF(J110,”short”, IF(J11=0,”No Stock”)))

    • Hui... says:

      @Nilesh
      I think your formula hasn’t posted correctly
      It should be something like
      =IF(J11 > =I11,”Available”, IF(J11 < I11,”short”, IF(J11 = 0,”No Stock”)))

      If you copy this you may have to retype the Quotation marks manually in the formula

  92. William says:

    I would like to create a drop down menu to select a certain item and when selected, data from multiple cells will be displayed (many cells from another worksheet). 
    Help please…
     

  93. Nilesh says:

    Dear Sir,

    We have one Stock file in excel, which is creating its backup file automaticaly, also the file continuously goes in recovery mode, if we copy paste all the data in oanother file then also we are facing the same problem.
    kindly advice to stop the backup file & recovery mode error.

  94. Harish says:

    Dear Chandoo sir,

    i have a data mentioned below:

    Designation     Basic      HRA        DA           Total      Name  ……………..
    Engg.                  25000     5000       3000        33000     Amit
    Engg.                  30000     7000       4000        41000     Ajay
    Driver                  5000      2000       1500          8500    Karan
    Operator            10000      4000       3000        17000    Jitender
    fieldboy               7000       3000       2500       12500     Anil

    & now i want to arrange above mentioned data in following sequence

    Name     Designation     Basic      DA       HRA       Total    

    Is there any other formula (function) to arrange data in desirable sequence without colomn movement ?
     
    Urgent Help please..

  95. tizzle says:

    I am trying to create a function based on example data below:

    Scan Time
    Trailer No.
    Scan Type

    553
    96579
    V

    803
    205134
    V

    714
    95125
    V

    730
    13393
    V

    729
    13393
    V

    707
    301242
    V

    I want the function to return a value of either ’1′, ’2′, ’3′ etc based on the van  (trailer) number in a seperate table with  the ‘line __” data. Basic.ally, I get a report with the data shown above and I want to be able to tell what line each van or trailer number can be found on. The seperate informational table would look something like below.

    Truck
              Line 
    City 
    Driver

     
     
     
     

    79936
    1
      Clawson 
    R.Smith

    86650
    3
       Snyder
    C.Lewis

    95151
    2
      Burton
    L.Gonzales

    Please help. How would you create the function and setup in excel?
    Thanks

    • Chandoo says:

      Hi Tizzle…

       

      I am not sure I understand this data. Can you send me a sample file at chandoo.d @ gmail.com so that I can provide an answer.

  96. Riley says:

    Hi Chandoo,
    I’m really impressed with your website.
    I need some help with my data tables. I have two tables: one sumerizing an old database and one summerizing a new database. Each table looks something like this :

    CompoundName     FileType1    FileType2    FileType3    Total
    Compound 1               1              0                   3            4
    Compound 2               0              0                   2            2
    etc..

    What I am trying to do is determine where the gaps in data are; if we have something in the old or new database that isnt in the other, per compound, per file type. Is there a way to do that in Excel?

    Thank you for your help.
    Riley 

    • MURALI says:

      by use of conditional formatting, you may get required info in one table duly highlighted with the colour formatted by you in the conditional formatting. you may use a formula like this where B is one table and H is corresponding cell in other table:
      =OR(B124=0,H124=0)

  97. roy says:

    Hi, I am looking for a solution using excel I have a block of data that is a running total( i got that), now I want to take the last total and place it in another chart and it will change with every change in the running total, I am not sure how to do this.

    • Riley says:

      You put an = then the name of the sheet with an exclamation point then the row/column number and it will reference that cell in that sheet, even if the content of the cell changes.
       
      Ex) =Sheet1!B5
       
       

  98. Kathy says:

    I have a spreadsheet that I want to tweek.  This is what I want it to do:

    When I place an “X” in column C, I want the text only in column A to go bold and turn red.

    Can this be done?  If so, how?
    Thanks    

  99. Etienne says:

    Why can’t I use defined dynamic range names in countif and sumif functions. Nor can’t I use it in conjunction with index and or indirect in specifiying my ranges.

  100. Mike says:

    Hi Chandoo,

    I just stumbled upon your website and have looked for a solution to my situation. You have a tremendous amount of information here!

    I am wanting to create either a conditional format or a formula to change a value from positive to negative based on a drop-down selection and the value itself.

    I created a drop-down list of E & O. (E means “even” and O means “odd”.)
    EO Selection
    E
    O

    Once this selection is made, I would like the values entered in the following cells to either become a positive or negative value based on the choice in the drop down box.

    for example:
    C1 is a drop-down box with only two items for selection. “E” or “O”.
    The cells below the drop-down box (C2-C7) can have any number entered.

    Scenario 1
    if “E” is chosen for C1;
    and the value entered in C2 is 3 (or any odd number)
    Then that value would be changed to negative
    C2 would then = -3

    Scenario 2
    if “O” is chosen for C1;
    and the value entered in C2 is 2 (or any even number)
    Then that value would be changed to negative
    C2 would then = -2

    Scenario 3
    if “E” is chosen for C1;
    and the value entered in C2 is 4 (or any even number)
    Then that value would be remain positive
    C2 would then = 4

    Scenario 4
    if “O” is chosen for C1;
    and the value entered in C2 is 5 (or any odd number)
    Then that value would be remain positive
    C2 would then = 5

    Would you be so kind as to share with me how does one accomplish this in excel?

    I would appreciate the assistance greatly.

    Thank you,

    Mike

    • Hui says:

      @Mike
      A simple Conditional Format can do the trick
      Select C2:C10
      Apply a CF using a Formula
      Set the formula to:
      =OR(AND($C$1=”E”,ISODD($C2)),AND($C$1=”O”,ISEVEN($C2)))
      Then set a Custom Number format of:
      -0.0;0.0;0
       
      Apply

      • Mike says:

        Hi Hui,

        Thank you for the assistance. Would you mind a bit more?

        I followed your instructions by cutting and pasting your formula. After making sure the cell range was correct, I also cleared all formatting and repeated the steps a few times. I did not have success in having the cf work in changing the polarity.

        All number values still appear positive when either E or O is selected. (1, 2, 3, 4, 5 & 6 are entered in C2:C7)

        E
        1
        2
        3
        4
        5
        6

        O
        1
        2
        3
        4
        5
        6

        (I tried to paste a screen shot of the CF screen to demonstrate my inputs, but this text area will not allow for that.)

        Any suggestions as to where to go from here?

        Once again, Thank you for the assistance.

        Mike

        • Hui says:

          @Mike
          A few ideas

          When Copy/Pasting the text for the formula from above you may need to re-type the ” as WordPress converts them to something that looks like ” but they aren’t

          Also the number format is applied in the CF not to the Cells

          Anyway the best way is to see the attached file: https://www.dropbox.com/s/arlx3r2q8qkup7o/EvenOdd.xlsx
           
           

          • Mike says:

            Hi Hui,

            It worked great! I guess the issue you described with the cut and paste was the culprit.

            After entering the formula and setting the custom number – it worked splendidly!

            Thank you!

            P.S. Do you know how to copy the formula across to 10 columns? When I have tried, the conditional formatting links it to the 1st E/O selection drop-down box.

            I can do this manually, but wondered if there was a faster method.

          • Hui says:

            @Mike
            Change the formula
            from: =OR(AND($C$1=”E”,ISODD($C2)),AND($C$1=”O”,ISEVEN($C2)))
            to: =OR(AND(C$1=”E”,ISODD(C2)),AND(C$1=”O”,ISEVEN(C2)))
            Apply the Custom Number format the same as before
             

  101. Mike says:

    Hi Hui,

    That worked great!

    Did you know, when using the sum formula to total the numbers in the column that the CF was applied, the sum is always positive. Why is that?

    Example:

    If I were to have a E choice for even, and have the following numbers in the cells below (-1, 2, -3, 4, -5, 6) the sums = 21! It seems to add the absolute values. It should = 3

    If I were to have a O choice for odd, and have the following numbers in the cells below (1, -2, 3, -4, 5, -6) the sums = 21! It seems to add the absolute values. It should = -3

    How do I sum the column to equal what it should be if one was to add the positive and negative numbers together?

    Thank you,

    Mike

    • Hui says:

      Yes
      How a number is displayed does not relate to the underlying number
      In your case I have used a Custom Number format which displays a Positive Number as a Negative Number according to your criteria, but the underlying number is still positive
       
       
       
       
       

  102. Banuchandar R says:

    Hi,
     
    How to check/verify duplicate number in excel
     
    Thanks
    Banuchandar.R

  103. Mike says:

    Hi Hui and everybody reading,
     
    I have been trying to find a solution for summing the numbers based on a condition.  (Please read my comments above from Jan 13 through Jan 16th) 
    The Conditional Formatting suggested by Hui is great.  THANKS HUI!  However, it will not allow me to sum the total in accordance with the negative or positive formatting.(The formatting is not applied to the numbers of the cells directly, but to the cell itself.)
    So, I guess I am back to the original intent of my first question.  If I have a choice of “Even” or Odd in cell C1 and C2-C7 are numbers that can be either odd or even how do I do the following:
    Chose Even in C1 so that any odd number in the range c2:c7 is not only displayed as a negative, but is also calculated as a negative in cell C8? 
    And Vice-versa Odd chose in C1, any even number in the c2:c7 cell range is negative, and calculated as a negative number in c8.
    The conditional formatting works great to display the numbers in the cell range c2:c7 as negative, but will not allow me to sum them algebraically.
    Example:
    C1 = “E” (for even)
    C2 = -1
    C3 = 2
    C4 = -3
    C5 = 4
    C6 = -5
    C7 = 6
    When I sum these cells the total in C8 = 21
    The answer I would like returned is = 3    [(-1) + 2 + (-3) + 4 + (-5) + 6]
     
    Is there a way to do this based on the E/O selection?  Conditional Formatting alone will not do it. 
    Your help is appreciated.
     
    Thank you,
     
    Mike

  104. shahbaz says:

    hi  i want to asign like d=12hr and d*=24
     
    is it possile tp to make a excell sheet

  105. Scorpion King says:

    Hie everyone,

    This is an awesome blog. I am very new to excel, I have recently started using excel in my daily work, I need a help.. the prob is

    If A is 100 and b is 200 ..
    If the b amount is less than A, then I want the result in a different cell as text as (we cannot proceed) , and if its greater than A , then (we can proceed )..

    Is there any formulae that can be used . Please guide.

  106. John says:

    Well if you’re the GOOROO then I am the one cell organism in the primordial slim.
    I am in South Sudan doing humanitarian work and trying to simplify the payroll hours data.
    I have never had a class or instruction in excel, so I am flying blind.
    My three data point for collection OverTime, Double OT, and Hours not completed (how many hours where not work to complete 40 hrs)
    What I am trying to avoid is displaying negative numbers. These are confussing to my Staff. if I use =SUM(D7:H7)-40 to figure my OT that works as long as the hours are over 40. But if they are under 40 I get a negative number and I would rather not have anything displayed unless it is over 40 hrs. Keeping the confusion down, because even though I know very little I am teaching them.
    The same goes for my “UnUsed Hours” If I use the same formula I get a negative number, WHich is the amount of unused hours but if I collect that negative number later and add it with the next weekss sheet and they are both negatives then I am realy getting confusing. Esspecially if they work over 40 then a positive number is displayed and that will really mess them up.
    So when I saw you “IF” formulas I thought maybe this could save me….!
    Can a formula be greated to say,” If over 40  then what is the total?” but not give me anything under the 40.
    And in reverse, “If under 40 what is the total? or If the total is under 40 what is it?” but donot display anything over fourty.
    The double OT is just adding the Sat and Sunday cells together.
    I am sure I am asking more than I should but I have always been told, “It is better to ask.”
    Thank You for any help you can offer.
     
     

    • Hui... says:

      @John
      =If(SUM(D7:H7)<40,0,SUM(D7:H7)-40)
      This says if the Sum of D7:H7 is less than 40 put zero otherwise put SUM(D7:H7)-40
      You can change 0 to anything you want
       

  107. Scorpion_King says:

    Hie everyone,
    This is an awesome blog. I am very new to excel, I have recently started using excel in my daily work, I need a help.. the prob is
    If A is 100 and b is 200 ..If the b amount is less than A, then I want the result in a different cell as text as (we cannot proceed) , and if its greater than A , then (we can proceed )..
    Is there any formulae that can be used . Please guide. 

  108. ikkin says:

    Hi,
    In your no. 5 example, i’m just wondering if what the -64 use in your formula =Choose(Code(D99)-64,”Excellent”,”Good”….. and if i will use that formula again how will I know if what to number to use?
    Thanks!

  109. sanjay says:

    Hi…

    First of all Thanks for all your excel tips….

    Here problem is…
    I was trying to get a sum of top five numbers (say assending order) from a data
    CAN YOU PLZ TELL ME HOW TO GET IT IN EXCEL

    thanks in advance

  110. DB says:

    Hi, I’m really a beginner so hold back your laughs.

    How do I create a “what if” to get a (%) of a sum to give me different values for a score? I need 90 – 99% of D29 to show 9, and 80 – 89% to score 8, etc.
    I can get my first step or score of 10. =IF(C29>D29,10)

    Thanks

  111. rRAJKUMAR says:

    iHi,
    I have a doubt in IF condition
    For Example:

    NAME WAGES CALCULATION USING IF
    EF 21000
    DGEDGF 25000
    GDVD 35000
    DGDV 18000
    DGD 56000
    DGVV 46000
    FGSDV 42000
    VAV 80000
    GASDG 35000
    FGSD 28000
    GDSDSG 38000
    DGDV 75000
    GAG 92000
    GA 88000
    GDG 46000
    GVD 76000

    BELOW 21000 – 0
    21001-30000 – 100
    30001-45000 – 235
    45001-60000 = 510
    60001-75000 – 760
    ABOVE 75001 – 1095

    HOW TO USE IF CONDTION FOR THIS TABLE

  112. Ben Nickless says:

    I currently have a spreadsheet that uses Countif(cell range,”*Value*”) to make text from one cell go white once the value in another in the range is entered to make the text look invisible, and now i want to make it so that if i enter a specific value to add a comment or to create a pop up box is this possible ?

  113. Melissa says:

    Hi Chandoo,
    First off, I have to say I absolutly love your tips! So informative and easy to understand with your examples!

    I have a project that I am currently working on, and am quite stumped. Hopefully you could have some tips/pointers?
    I am creating an employee scheduling spreadsheet and am unsure how to lay out my data and create the formulas. I have considered an extremely length if statement but am not sure if it will get the job done. Ill break down my variables; depending on the projected ticket sales(a small medium or large day), depending on what day of the week, depending on the show schedules for that day, we will need to staff accordingly.

    You brought up a good tip of the choose function. Do you think it will be applicable in my situation? I can upload my data; I am really quite stumped and can use all the advice, tips and pointers!!!

    Many thanks!

  114. Melissa says:

    Hi Chandoo,
    First off, I have to say I absolutly love your tips! So informative and easy to understand with your examples!

    I have a project that I am currently working on, and am quite stumped. Hopefully you could have some tips/pointers?
    I am creating an employee scheduling spreadsheet and am unsure how to lay out my data and create the formulas. I have considered an extremely length if statement but am not sure if it will get the job done. I have multiple variables: the estimated ticket sales(sm med or large), day of the week, show schedules for that day, staff availibility hrs, etc

    You brought up a good tip of the choose function. Do you think it will be applicable in my situation? I can upload my data; I am really quite stumped and can use all the advice, tips and pointers!!!

    Many thanks!

  115. Monnequeen says:

    Hi. I hope you can help me. This will make my life easier when rating 100+ employees.

    I have 2 computation tables (1. team-based; 2. personal performance) that contain the equivalent monetary figure for a point grade (incentive scheme) that is earned. Now, I want to come up with something where I just input the point grades and the sum total in monetary figures will be the result of the computation. I’ve set up the table that I just need to input the monthly salary and the figures in the table will change accordingly.

    Team Pts Me Points Total Incentive in Php
    94 99 ?????

    The table looks like this

    (Assumption on Monthly Salary: Php 30,000.00)

    Team Pt #Days (70%) Php Me Point #Days (30%) Php
    100 75 52,500 100 75 22,500
    99 74.25 51,975 99 74.25 22,275
    97 73.51 51,455.25 98 73.51 22,052.25
    and so on and so forth

    Hope you can help me. Thanks.

  116. Brian says:

    You can go beyond 7 nested “if”‘s by using &. See below, see where B1=6,”JUN”,””) I added the & after the bracket:
    =IF(B1=1,”JAN”,IF(B1=2,”FEB”,IF(B1=3,”MAR”,IF(B1=4,”APR”,IF(B1=5,”MAY”,IF(B1=6,”JUN”,””)&IF(B1=7,”JUL”,IF(B1=8,”AUG”,IF(B1=9,”SEP”,IF(B1=10,”OCT”,IF(B1=11,”NOV”,IF(B1=12,”DEC”,””))))))))))). You can keep on going many times. Can get messy I do admit, but it’s possible.

  117. Daniel says:

    Well this just made my math project a heck of a lot easier.

    Thanks!

  118. Akshay Wagle says:

    Sexy tips man!
    Will be of great help to the analysts and consultants. :)

  119. Manu says:

    Hi, need help on below where am trying to built a logic where i need the details of duplicate tickets registered when there is already an Open ticket.

    For example
    Sl# Account # Registered Date Closed Date
    1 545471 28-06-2013 22:18:00 29-06-2013 10:38:24
    2 545471 28-06-2013 22:19:00 29-06-2013 12:19:55
    3 545471 28-06-2013 19:23:00 29-06-2013 14:36:07

    I need a formula where i should get the 2nd Sl# as output as the same was registered even though the 1st Sl# Account was in Open status, please help me out

  120. Noman says:

    i want to create roll numbers automatically…like if i enter name of the new student then the roll number row automatically generate new number from the series…
    it is like if i put name then it create else it remain 0..
    can any body help me plzzzz.

  121. Chandoo says:

    Testing Disqus comments…

  122. […] Later when I joined B-School, I had to learn how to use formulas like IF() to model real world situations. And boy oh boy, that proved to be a very difficult experience. I still remember that one afternoon when I spent more than 2 hours trying to debug the IF() formula. […]

  123. Toma says:

    I am working on a scorecard in Excel and I’m having some trouble with the traffic lights for KPIs that the trend is supposed to be within range. Meaning that the IF formula i’m currently using does not currently work right. I have the following fields: Ceilling, Target, Threshold and Floor, which are the interval values for the KPI results. Any idea how i can work them in order to show me the right traffic light?

    Thanks.

    • MURALI says:

      Please mention the % , or number limits for your fields , so that the traffic lights can be formulated using conditional formatting.

  124. Hi, i think that i noticed you visited my web site so i got here to go back the choose?.I am trying to in finding
    things to enhance my website!I assume its adequate to use some of your ideas!!

  125. samoya says:

    How do i write a code to say:
    If there’s only one row, then return the value on that row, but if there’s multiple rows return the sum of the values in the rows.

  126. hamzeh says:

    cm popularity mm rating
    low high
    i have those two columns and i need the third column to be if high and high the third column writes good and if low and high medium and if low and low bad

    • MURALI says:

      use formula in 3rd col ie in C3 as:
      =IF(COUNTA(A1,B1)<=1,"",IF(COUNTIF(A1:B1,"high")=2,"good",IF(COUNTIF(A1:B1,"high")=1,"medium",IF(COUNTIF(A1:B1,"low")=2,"bad",""))))

  127. Randy says:

    I can’t get the following two formulas to work for my downloaded bank info:
    =SUMIF(A2:A1530,CREDIT,D2:D1530)
    =SUMIF(A2:A1530,DEBIT,D2:D1530)
    Where Column “A” is the type of transaction and Column “D” is the amount.

  128. Chris says:

    i want to apply the formula from one cell to another cell using the function IF,OR. but one of the cell is already formulated with the data validation for e.g in a cell there are names like Alfred, Bryan, Charlie(which is formulated) and i want the city name (i.e Mumbai, Delhi, Chennai) to be automatic populated in the different cell, like wise if i select Alfred from one cell the name Mumbai should automatically appear in another cell.

  129. Meni Porat says:

    Hi Chandoo,

    I like your tips very much. Thank you for an extremely useful site.

    Referring to the first trick (At the top of this long page):
    1. sum alternative rows/columns.
    Your trick of adding a series of 0s and 1s to sum alternate rows is awesome. But I didn’t like the 0s and 1s “hard-coded”, so I composed the following formula instead:
    =IF(INT(ROW()/2)*2=ROW(),1,0)

    What the formula does:
    It finds the integer number (INT) of the row number after dividing it by 2 (ROW()/2) and then multiplies it by 2.
    If the row number is an even number, then the result of dividing by 2, finding the integer and then multiplying by 2 will render a number equal to the original ROW(). For example, if ROW() is 4, then INT(4/2)* 2 = 4. Therefore, the result of the formula is 1 (true).
    However, if the row number is an odd number (i.e. 5), then INT(5/2)*2 = 4, and we all know that 4 5. Therefore, the result of the formula will be 0 (false).

    After writing the formula in the first cell (in your example D7), all you have to do is double click the bottom right corner of cell D7 and the formula will be copied all the way down (in your example D26),
    et… voila, c’est tout…

    I admit that this a long, cumbersome solution. Nevertheless, it demonstrates one of the goals of your original article, which is: An IF trick you didn’t know….
    ?

  130. Meni Porat says:

    Another (simpler) solution to get alternate 1s and 0s in a column (instead of hard-coded 1s and 0s) is:

    =IF(ROW()=ODD(ROW()),1,0)

  131. Massimiliano says:

    Dear all, in a homework for INDEX(MATCH()) there is a question

    Questions
    1. Who sold second highest?

    Sorry, but is there a specific formula to indicate the 2nd, the 3rd, etc MAX?

    Thanks in advance
    Massimiliano

    • MURALI says:

      Yes. formula for who:
      =INDEX(array,MATCH(LARGE(array,k),lookup_array,0),MATCH(value,lookup_array,0))

      eg to find Second Highest:
      =INDEX($H$54:$J$69,MATCH(LARGE($J$54:$J$69,2),$J$54:$J$69,0),MATCH($H$53,$H$53:$J$53,0))

  132. Bing says:

    I was trying to search the whole day for the following scenario and i came across to these site..which i believe someone can answer,

    Column 1 Column 2 Column 3
    Item Code On Hand Cases
    Item1 10
    Item1 10
    Item1 10
    Item2 5
    Item2 5
    Item2 5
    Item3 5

    My question is, how to get the cumulative sum by item in column 3.
    I hope anybody can assist and teach me the correct excel formula for these.Thank you in advance.

  133. Bing says:

    Great..it works!
    Thanks a lot, more power to you.

    :)

  134. Gina says:

    I need some help!! I need a formula that will allow me to view the percentage of completion based on the amount of material.
    For instance I have 19 parts that are used to build a certain component. When I have all 19 parts this is 100% complete. but if I have 8 parts I am only at 50%. How can I write a formula for this??

    • Hui... says:

      @Gina
      The Formula is =100*8/19
      which isn’t 50% it is 42.1%

      In Excel you can use a formula
      =8/19
      You don’t need the 100* because you can use Excel to Format the cell as a Percentage by clicking on the % button in the toolbar

      If your values are in cells
      Say
      Value used 8 is in A1
      Total to be used 19 is in A2
      You can use: =A1/A2

Leave a Reply