SubscribeLearn Excel IF Formula – 5 Tricks you Didnt know
Excel 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”.
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]
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)
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…
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:
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:
- Become a conditional formatting rock star – learn these 5 cool tips
- Using COUNTIF and SUMIF formulas
- Using SUMIF or SUMPRODUCT formulas to sum with multiple criteria
| ||
|
Spread some love,
Posts & Navigation
Tags: choose(), countif(), downloads, howto, if(), if() excel formula, Learn Excel, list posts, microsoft, Microsoft Excel Formulas, spreadsheets, sumif(), technology, vlookup |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
104 Responses to “Learn Excel IF Formula – 5 Tricks you Didnt know”
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
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
Nice tips, most of them will be quite useful to me
@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
[...] Original post by Chandoo [...]
[...] 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, [...]
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
@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
[...] 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 [...]
[...] 6 Things you dont Know about IF() Formulas [...]
[...] Cleaning up data using countif(), sumif(), if() formulas [...]
[...] 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 [...]
[...] Using countif() and auto filter [...]
[...] 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 [...]
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
@ 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….
@ 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
@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.
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
@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.
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
[...] What the IF? – learn 6 cool things you can do with excel if() functions [...]
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
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
@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.
Thanks Chandoo
for the Work around, solun I will try implement it .
Happy Holiday
regards
Satish K
[...] 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 [...]
[...] 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. [...]
Thank a lot for this.I appreciate your work..
God Bless You..!
[...] excel formulas: IF and Then, Vlookup, Offset, Sumif, Countif, Working with date and [...]
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
@Ghost : you can simply write a formula like =(21-a1)*5 assuming a1 has the place.
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??
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).
@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 ?
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.
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!
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!
@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.
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!
@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/
Thanks for choose function tip. Very useful. Nice site for everyone trying to create within excel. Success…:)
[...] IF Formula & 5 Tips [...]
Thnx Chandoo.. thz ws vry useful….. thnx a lot…!!!!!!!
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…
[...] go ahead and use IF formula, if that is what you need to [...]
[...] 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 [...]
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
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.
@Dave
=SUMPRODUCT(–(LEFT(A2:A100,3)=”PRT”))
This isn’t case sensitive
@Dave.. also try =COUNTIF(A1:A15,”PRT*”) to count values that begin with PRT
Thanks Hui & Chandoo for all your help!
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
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 …
@Shayan
Make up a Table in say A1:B7 as below
Then use a VLookup to retrieve your grades:
=VLOOKUP(E1,A1:B7,2,TRUE)
Where E1 has the score
=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
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
thanks thanks thanks its working great hui thanks a lot
@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
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
@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
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?
@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")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
@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
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?
What values do you have in B22:F22 ?
Please paste your formula in your response also
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
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”)Hui,
Many thanks for your help. You have really granted me a great relief.
Meanwhile, there is this
Hui,
Many thanks for your help. You have really granted me a great relief.
he is a genius in excel.
master minddddddddd
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
@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)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
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)Hi Hui,
I have tried this but I am having ‘#Name’ error
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.
@Yinkus
What was the solution ?
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.
@Sriram
Looks like you should be using and Index/Match combination
Have a read of http://www.contextures.com/xlfunctions03.html#IndexMatch
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
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.
@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
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,))))))))
@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)
Brilliant, worked a treat. Many thanks.
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.
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
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.
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
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
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
GOOOOOOOOOD JOB
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
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”)))
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.
@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.
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
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…
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”)))
@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
thanks for reply
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…