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 nest if functions beyond 7 levels (in earlier versions of Excel)? 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.
226 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 🙂
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
I have a question in choose formula. I do not know where you are getting 64.
Regards,
Akbar
Code(A) returns 65, so by subtracting 64 he's bringing the value to 1 - which is required for the CHOOSE(). Clever trick =)
[...] 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
how it could be possible that i assigned a limit "250" , and a cell show only 200 in this cell and above value than 200 show in other cell
[...] 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...
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.
[...] 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.
1st col is assumed as Sl. No. Then try the formula IN D2 as
=CONCATENATE(B2," min ",C2," sec")
@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")))
use formula as
=IF(J2>=H2,1,IF(J2>0,"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...
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.
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..
@Harish
Add a row above the data
Insert Numbers above the "Designation Basic HRA DA Total Name" etc in the order you wat them
select the whole of your data including the new numbers above it
Sort it, use a Horizontal Sort instead of a vertical sort
Delete the Row you added
thankyou Hui Sir,
its working...........thanks alot
@Harish
🙂
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
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.
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
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)
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.
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
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
@Kathy
What you want to do is called Conditional Formatting
Chandoo has many articles on that, just use the Google Search Box (Top Right of every page)
You may want to start here: http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/
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.
@Etienne
It sure is possible to use Dynamic Ranges with all those functions
Can you maybe post some of the formulas or Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
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
@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
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
@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
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.
@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
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
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
Hi,
How to check/verify duplicate number in excel
Thanks
Banuchandar.R
@Banuchandar
Chandoo ga written many posts on Duplicates
Did you try typing duplicate in the Google search box at the Top Right of the page
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
hi i want to asign like d=12hr and d*=24
is it possile tp to make a excell sheet
use formula =IF(B140="d","12 hr",IF(B140="d*","24 hr",""))
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.
yes, use formula: =IF(B1<A1,"we cannot proceed","we can proceed")
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.
@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
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.
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!
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
use this formula =sum(large(array,5),large(array,4),large(array,3),large(array,2),large(array,1))
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
@DB
A formula like
=Int(A2/0.1)
should do the trick
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
use formula as below : =IF(B2<20001,0,IF(B2<30001,100,IF(B2<45001,235,IF(B2<60001,510,IF(B2<75001,760,1095)))))
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 ?
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!
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!
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.
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.
Well this just made my math project a heck of a lot easier.
Thanks!
Sexy tips man!
Will be of great help to the analysts and consultants. 🙂
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
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.
you may use this formula:
=IF(A4="",0,B3+1)
Testing Disqus comments...
[…] 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. […]
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.
Please mention the % , or number limits for your fields , so that the traffic lights can be formulated using conditional formatting.
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!!
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.
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
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",""))))
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.
Put " around the words Credit and Debit
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.
use this formula
=IF(A1=G1,H1,IF(A1=G2,H2,IF(A1=G3,H3,"")))
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….
?
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)
@Meni
An even simpler formula is
= - -(ROW()=ODD(ROW()))
or
= - -ISODD(ROW())
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
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))
Thanks a lot for this kind help, today I try them!
Best regards
Ciao
Massimiliano
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.
@Bing
In C2: =SUMIF($A$2:A2,A2,$B$2:B2)
Copy down
Hui...
Great..it works!
Thanks a lot, more power to you.
🙂
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??
@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
Im trying to create a function that can highlight various amounts related to a single accumulated amount.
for example:
From below data I want to find various amounts that equals to 1870. What function can i use.
50
250
340
348
500
673
740
Hello Chandoo,
I am learning excel tricks from your website and I found very useful. Kindly help me out in this. I want to do text arrangement in an alternative rows. Suppose there is column till A100 with two name like google search and Google partner but are mixed. Now I want to arrange these two networks in an alternative rows so that I can use the formula (Sum alt rows/column) and get the value instant. Is this possible? Please help.
i want to learn Excel from base ..............please help & suggest.
I have a log sheet that I edit at the end of a production run. I need to calculate the percentage of good parts that each shift made. The issue that I am having is I don't know how to have excel look at the shift column and calculate it for the entire run. The log has multiple shifts and each shift multiple times. I can send you a copy of the spreadsheet for you to look at.
Hi Chris,
You can send a copy of the file with dummy data to chandoo.d@gmail.com I am glad to help you and use this file for a future article.
Hi Chandoo,
could you please help in solving my problem
my excel column contains -5:55 -2:26 -2:19 -1:38 1:06 1:16 1:17 2:20 like wise nearly more than 100
my result column to have Neg Neg Neg Neg 1-2 HR 1-2 HR 1-2 HR 2-3 HR
could you please provide me an IF formula
am using 2007 excel
Remaining-TAT(h:m) TAT
-2:19 Neg
-1:38 Neg
1:06 1-2 HR
1:16 1-2 HR
1:17 1-2 HR
1:20 1-2 HR
2:02 2-3 HR
2:05 2-3 HR
Thank you
@Ramesh
Assuming your data is in B2:B100+
In C2: =IF(LEFT(B2,1)="-","Neg",LEFT(B2,FIND(":",B2)-1)&"-"&LEFT(B2,FIND(":",B2)-1)+1&" Hr")
Copy C2 down
If you copy this from here you may need to re-type the " marks as sometimes they get screwed up
Hi Chandoo, Hui,
could you please help in solving my problem
my excel B column contains -13:31 -13:12 -11:56 1:16 1:17 1:20 2:02 2:05 3:07 3:09 7:00
8:00 8:02
like wise nearly more than 100 are there
my result C column to have #NAME? Neg Neg 1-2 HR 1-2 HR 1-2 HR 2-3 HR 2-3 HR 3-4 HR 3-4 HR 7-8 HR >8 HR >8 HR
could you please provide me an IF formula or some formula so that i can paste formula in C column
Time(hh:mm) final Needed
-13:31 #NAME?
-13:12 Neg
-11:56 Neg
1:16 1-2 HR
1:17 1-2 HR
1:20 1-2 HR
2:02 2-3 HR
2:05 2-3 HR
3:07 3-4 HR
3:09 3-4 HR
7:00 7-8 HR
8:00 >8 HR
8:02 >8 HR
with Hui formula am getting #NAME?
I wanted to use the Choose function because my nested If formula even though there are only 3 conditions is a huge formula. I have a column of cycle time which is a formula that calculates the difference between 2 dates. The formula I have compares the calculation of the cycle time with another cell which has the calculated average cycle time and if it is at or below the average I want it to return "at or below average" but if it is above the average but below 6 days to say "Above average" but it is above 6 days to say "Above 6 days-Extenuating". The problem I am having is if one of the dates is blank the cycle time cell is blank but the nested if program doesn't count it as blank it looks at the formula and returns "Above 6 days-Extenuating" but I want it to return blank. How can I get it to ignore the formula and only return values based on the results?
Hi Chandoo, Hui,
could you please help in solving my problem
my excel B column contains -13:31 -13:12 -11:56 1:16 1:17 1:20 2:02 2:05 3:07 3:09 7:00
8:00 8:02
like wise nearly more than 100 are there
my result C column to have #NAME? Neg Neg 1-2 HR 1-2 HR 1-2 HR 2-3 HR 2-3 HR 3-4 HR 3-4 HR 7-8 HR >8 HR >8 HR
could you please provide me an IF formula or some formula so that i can paste formula in C column
Time(hh:mm) final Needed
-13:31 #NAME?
-13:12 Neg
-11:56 Neg
1:16 1-2 HR
1:17 1-2 HR
1:20 1-2 HR
2:02 2-3 HR
2:05 2-3 HR
3:07 3-4 HR
3:09 3-4 HR
7:00 7-8 HR
8:00 >8 HR
8:02 >8 HR
with Hui formula am getting #NAME?
My Question still Pending please
@Ramesh
Please ask the question in the Chandoo.org Forums and attach a sample file
http://chandoo.org/forum/
As my formula works on my data, which obviously isn't the same as yours.
@Ramesh
In my previous post above:
In C2: =IF(LEFT(B2,1)="-","Neg",LEFT(B2,FIND(":",B2)-1)&"-"&LEFT(B2,FIND(":",B2)-1)+1&" Hr")
Copy C2 down
If you copy this from here you may need to re-type all the " marks as sometimes they get screwed up
I hope you took notice of the Bold line
Hi Chandoo,
I have a master data and I want to create auto generated report referring that master data.
Master data :
Version Date Decision Comments Approver Name
Rejected
Rejected
Approved
This is a data format. Now my auto generated report has one cell saying "Status".
I want to fetch the status from master data from "Comments" column whether is it "Approve" or "Rejected"..
Note that there version no is unique and not the dates.
Awaiting your reply.
Regards,
Prajakta
I need to set function in excel. That if cell Value is "A" , then present cell value should be "X"
If cell value is "B" , then present cell value should "Y"
@Vinod
Lets assume that you are referring to cell A1
Use =If(A1="A","X", If(A1="B","Y","-"))
This will put a - if the value isn't A or B
If you can guarantee it will always be A or B then use
=If(A1="A","X", "Y")
Good Day.
Hi Sir.
I just want ask if you can give the formula on how do i separate the name and place it on one column and the other one column is the Tax identification number
SOUTH LUZON TOLLWAY CORPORATION VAT 288-247-77
MA CHICKEN MAMI HOUSE VAT 209-677-172
CAUSEWAY RESTAURANT NV 210-854-469
DHL EXPRESS CARGO VAT 212-186-731
So the result would be like this;
SOUTH LUZON TOLLWAY CORPORATION 288-247-77
MA CHICKEN MAMI HOUSE 209-677-172
CAUSEWAY RESTAURANT 210-854-469
DHL EXPRESS CARGO 212-186-731
or if not just give me a formula on how to separate the letters from the numbers (xxx-xxx-xxx).
This would be a great help on my everyday excel works in the office. Thank you Sir.
@JPCPA
Assuming your data is in a1:A5
B1: =LEFT(A1,LEN(A1)-LEN(C1)-LEN(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-LEN(C1)-1)," ",REPT(" ",100)),100)))-2)
C1: =TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))
Copy down
If you copy these re-type the " characters as they get corrupted here
I am 38 years old....I taught myself what i know in excel / word etc....
I need help on improving please...
I have a front sheet that has date client weight and then amount then cost column.
on the second sheet i have invoices from the first one to the last underneath each other i always just copy and paste another underneath the invoice number changes as i have formulas there.
what i need is to pull the data from the invoice to the front sheet where i have the summary without having TO RE ENTER THE DATA
@Debra
Please ask the question in the Forums and attach a sample file
http://chandoo.org/forum/
I want to use IF formula with one excel book to another work book. i am giving an example. (there are two different work book) please mail me on this email id with excel sheet.
This is book -1
a b c
ak singh 0 1 0
mk singh 0 1 1
This is Book -2. Headings are same in 1st & 2nd book. now i want to use "if" behalf of 1 & 0, i want to take "yes" & behalf of blank colum or row i want to use "NO"
a b c
ak singh
mk singh
STUDENT NAME MARKS GRADE
RICHARD NELSON 67 C
DANIEL OWEN 88 A
MARTHA KELLY 65 C
GEORGE ENOCK 77 B
ALLAN BECKS 71 C
From the information given above how can i extract the highest in this case DANIEL OWEN his marks and grades?. which formula to use to get GEORGE ENOCK as second from highest.......I am confused on the combination of IF,,,,Max,,,,Vlookup, concantenate,,,which is which and how to compe up with the formula..please help.
STUDENT NAME MARKS GRADE
RICHARD NELSON 67 C
DANIEL OWEN 88 A
MARTHA KELLY 65 C
GEORGE ENOCK 77 B
ALLAN BECKS 71 C
From the information given above how can i extract the highest in this case DANIEL OWEN his marks and grades?. which formula to use to get GEORGE ENOCK as second from highest.......I am confused on the combination of IF,,,,Max,,,,Vlookup, concantenate,,,which is which and how to compe up with the formula..please help.
Reply
i copied your data and is found in range A1 to C6
to get the 3 data, put the ff formula in 3 different cells
Highest grade :
=INDEX(A:A,MATCH(MAX(B:B),B:B,0)) -->daniel (put formula in E3)
=MAX(B:B) --> 88 (put formula in F3)
=VLOOKUP(E3,A:C,3,FALSE) -->A (put formula in G3)
Second Highest grade:
=INDEX(A:A,MATCH(LARGE(B:B,2),B:B,0)) -->george
=LARGE(B:B,2) --> 77
=VLOOKUP(E4,A:C,3,FALSE) -->B
ok...i didnt understood anythinf...lol
anything*
Hi Sydney
To get the maximum, you can use the max function.
To get the rank you can use the Rank function.
Considering that you have marks in cells b2:b6 and you are looking for the ranks in column d, the formula in d2 would be rank(b2,$b$2:$b$6). Then copy this and paste it from d3 to d6.
I need to put in a sum in a budget spreadsheet,
I have got this one to work:
=IF(ISERROR(VLOOKUP(C25,'February 2016'!A$1:C$65536,2,FALSE)),0,VLOOKUP(C25,'February 2016'!A$1:C$65536,2,FALSE))
"February 2016' is a sheet name and I want to be able to vary this sum easily to other sheets in the workbook.
I was thinking of using a data list in a cell and referencing that but it is not working. Any help please.
i have a big worksheet (8000 row) of some address but there is no gap between 2 address but the name row height is high then other row i want to inset one row between tow address and want to make name(first row of all address) Bold. can i use IF formula.can formula like this work
if height of row is higher then xxx insert one row and make tax bold
Hi - I perused these help comments, and maybe I missed it, but I'm trying to come up with a simple IF THEN statement that accomplishes the following:
IF the answer in that cell is YES
THEN
You must enter an answer here in this cell (which is unformatted text of not more than 25 characters).
(If the answer is NO, then nothing needs to be entered.)
Has anyone come across anything similar? Did I miss it?
This seems like a really informative page.
Thank you!
I need a alternative to following formula I devloped.
=IF('Day 9'!L30=1,'Day 9'!$E30,0)+IF('Day 10'!L30=1,'Day 10'!$E30,0)+IF('Day 11'!L30=1,'Day 11'!$E30,0)+IF('Day 12'!L30=1,'Day 12'!$E30,0)+IF('Day 13'!L30=1,'Day 13'!$E30,0)+IF('Day 14'!L30=1,'Day 14'!$E30,0)+IF('Day 15'!L30=1,'Day 15'!$E30,0)
I generated this formula to sum value of a particular cell in different sheets if the value of a cell in the same sheet is equal to say 1. the summation will be in a completely new sheet
Please help me, I want to add formula in my working. Case is as follows:
If a person achieves 100% target of Product A then (Product A has two pack sizes 1st is A1 & 2nd is A2) incentive will be 0.55 for A1 and 0.75 for A2.
If a person achieves 80% to 99% target of Product A then (Product A has two pack sizes 1st is A1 & 2nd is A2) incentive will be 0.44 for A1 and 0.60 for A2.
Product A total target is = 80
A1 target is = 50
A2 target is = 30
Please help me and guide me that how I will fix this problem with single formula rather than doing is separately
Please help
I need some help here:
I have 3 columns of dates A,B,C and I want the result in D.
If C has a date then I automatically want that to be placed in D, but if C is blank I want it to take the date in B and add 30 days, but if C and B are blank I want it to take A and add 30 days
@Sherry
in D2: =IF(C2>0,C2,IF(B2+C2=0,A2,B2)+30)
Copy down
Thank you so much!
Hello,
I'm fairly new to excel although I have read "Excel: Quickstart Guide From Beginner to Expert". The book was helpful but I'm still in need of more info.
My question is this:
I am creating a cash flow for the next two years. I want my electricity bill to alternate between $70 in fall/winter months and $200 in summer months.
I tried entering: =IF(C2>DATEVALUE(May 31),70,200) and it didn't work.
If you could help me figure out how to enter the correct function I would greatly appreciate it! Also, if there is something I could read to help gain more general knowledge about Excel I would appreciate any tips.
Thanks
10 Pkt - Gopi Krishna
10Pkt - Gopi Krishna
i need to extract number pls advice
also help me whn i have different pin code i have to have different state name eg 411009 pune then state name should be maharashtra
good analysis of the different type of situations using a formula with examples thanks a lot
Dear alll,
i am cant return to values except IFS(CU530,(AE5-((AE5-AG5)/5)*(CU5-30)) in below mentiond formula please clarify
IFS(CU530,(AE5-((AE5-AG5)/5)*(CU5-30)),CU535,(AG5-((AG5-AI5)/5)*(CU5-35)),CU525.5,(AA5-((AA5-AC5)/1.5)*(CU5-25.5)),CU527,(AC5-((AC5-AE5)/3)*(CU5-27)))
IFS(CW530,(AG5-((AG5-AI5)/5)*(CW5-30)),CW535,(AI5-((AI5-AK5)/5)*(CW5-35)),CW525.5,(AC5-((AC5-AE5)/1.5)*(CW5-25.5)),CW527,(AE5-((AE5-AG5)/3)*(CW5-27)))
Hi,
I am FAIRLY NEW TO Excel. I am creating a cash flow for the next two years for my company. I want my electricity bill to alternate between $70 in the fall/winter months and $200 in summer months.
I tried entering: =IF(C2>DATEVALUE(May 31),70,200) and it didn't work. Please could you help me with the explanation?
Great !Nice read the differentiation between IF and CHOOSE function. Thanks.