Excel IF AND OR functions on their own aren’t very exciting, but mix them up with the IF Statement and you’ve got yourself a formula that’s much more powerful.
In this tutorial we’re going to take a look at the basics of the AND and OR functions and then put them to work with an IF Statement. If you aren’t familiar with IF Statements, click here to read that tutorial first.
IF Formula Builder
Our IF Formula Builder does the hard work of creating IF formulas.
You just need to enter a few pieces of information, and the workbook creates the formula for you.
AND Function
The AND function belongs to the logic family of formulas, along with IF, OR and a few others. It’s useful when you have multiple conditions that must be met.
In Excel language on its own the AND formula reads like this:
=AND(logical1,[logical2]....)
Now to translate into English:
=AND(is condition 1 true, AND condition 2 true (add more conditions if you want)
OR Function
The OR function is useful when you are happy if one, OR another condition is met.
In Excel language on its own the OR formula reads like this:
=OR(logical1,[logical2]....)
Now to translate into English:
=OR(is condition 1 true, OR condition 2 true (add more conditions if you want)
See, I did say they weren’t very exciting, but let’s mix them up with IF and put AND and OR to work.
IF AND Formula
First let’s set the scene of our challenge for the IF, AND formula:
In our spreadsheet below we want to calculate a bonus to pay the children’s TV personalities listed. The rules, as devised by my 4 year old son, are:
1) If the TV personality is Popular AND
2) If they earn less than $100k per year they get a 10% bonus (my 4 year old will write them an IOU, he’s good for it though).
|
In cell D2 we will enter our IF AND formula as follows:
In English first
=IF(Spider Man is Popular, AND he earns <$100k), calculate his salary x 10%, if not put "Nil" in the cell)
Now in Excel’s language:
=IF(AND(B2="Yes",C2<100),C2x$H$1,"Nil")
You’ll notice that the two conditions are typed in first, and then the outcomes are entered. You can have more than two conditions; in fact you can have up to 30 by simply separating each condition with a comma (see warning below about going overboard with this though).
IF OR Formula
Again let’s set the scene of our challenge for the IF, OR formula:
The revised rules, as devised by my 4 year old son, are:
1) If the TV personality is Popular OR
2) If they earn less than $100k per year they get a 10% bonus.
|
In cell D2 we will enter our IF OR formula as follows:
In English first
=IF(Spider Man is Popular, OR he earns <$100k), calculate his salary x 10%, if not put “Nil” in the cell)
Now in Excel’s language:
=IF(OR(B2="Yes",C2<100),C2x$H$1,"Nil")
Notice how a subtle change from the AND function to the OR function has a significant impact on the bonus figure.
Just like the AND function, you can have up to 30 OR conditions nested in the one formula, again just separate each condition with a comma.
Try other operators
You can set your conditions to test for specific text, as I have done in this example with B2="Yes", just put the text you want to check between inverted comas “ ”.
Alternatively you can test for a number and because the AND and OR functions belong to the logic family, you can employ different tests other than the less than (<) operator used in the examples above.
Other operators you could use are:
- = Equal to
- > Greater Than
- <= Less than or equal to
- >= Greater than or equal to
- <> Less than or greater than
Warning: Don’t go overboard with nesting IF, AND, and OR's, as it will be painful to decipher if you or someone else ever needs to update the formula in months or years to come.
Note: These formulas work in all versions of Excel, however versions pre Excel 2007 are limited to 7 nested IF’s.
Download the Workbook
Enter your email address below to download the sample workbook.
Excel IF AND OR Practice Questions
IF AND Formula Practice
In the embedded Excel workbook below insert a formula (in the grey cells in column E), that returns the text 'Yes', when a product SKU should be reordered, based on the following criteria:
- If Stock on hand is less than 20,000 AND
- Demand level is 'High'
If the above conditions are met, return 'Yes', otherwise, return 'No'.
Tips for working with the embedded workbook:
- Use arrow keys to move around the worksheet when you can't click on the cells with your mouse
- Use shortcut keys CTRL+C to copy and CTRL+V to paste
- Don't forget to absolute cell references where applicable
- Do not enter anything in column F
- Double click to edit a cell
- Refresh the page to reset the embedded workbook
IF OR Formula Practice
In the embedded Excel workbook below insert a formula (in the grey cells in column E) that calculates the bonus due for each salesperson. A $500 bonus is paid if a salesperson meets either target in cells C24 and C25, otherwise they earn $0 bonus.
Want More Excel Formulas
Why not visit our list of Excel formulas. You'll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy 🙂
Excel Nested IF’s Explained
Aymen T.
Hell Sir I am working on a sheet (data given below). I have a time and want to check if it falls between a given time range or not. The formula i am using is “If(AND(C72>A72,C72<B72), "TRUE", "FALSE")". Column C is extracting value from another timestamp column. It works (Column D) if I add the value manually in Column C but it doesnt work on automatically extracted value. Please help!
A B C D
11:33:48 11:53:48 12:50 FALSE
12:20:22 12:38:21 12:21 TRUE
12:39:27 13:00:09 12:40 TRUE
14:16:23 14:20:49 14:17 TRUE
14:20:54 14:22:56 14:21 TRUE
4:00:00 16:00:00 12:00:00 TRUE
Catalin Bombea
Hi Aymen,
How are you extracting the values automatically?
Normally, if calculation is set to Automatic, formulas are recalculated when the source cells change.
Aymen Tasneem
I am taking the timestamp if certain column is filled, formula is “IF(H36″”,IF(U36″”,U36,NOW()),””)”
Catalin Bombea
Can you please provide a sample file that replicates the problem on our forum? (create a new topic)
Joy
I need your help in one topic.
As the Sumifs function does the different criteria with Different range. I need the same in IF condition Kindly help.
For instance
Joy is the employee and he does three task need to check the condition in every task he must have quality of above 98%.
If quality is less than 98% in any of the three task it must return value as Quality is Low for all three columns else if quality is more than 98% for all need to return as Selected in all three columns.
Consider below as given in excel sheet
Name task quality Remarks
Joy. Adhoc. 99.
Joy. Pinning. 97.
Joy. Tagging. 100.
Kindly please help me with the condition
Philip Treacy
Hi Joy,
if your Quality column is D3:D5 then try this
=IF((COUNT($D$3:$D$5)/COUNTIF($D$3:$D$5,”>98″))=1,”Selected”,”Quality is low”)
Note that you haven’t said what do do when the Quality score equals 98.
If you are still stuck please start a topic on our forum and attach your example file with data.
Regards
Phil
Michael Cruz
Hi there! I just wanna seek help with this problem I encounter regarding prevention of duplicate entries. Here’s the scenario if the “couple with the same name and same birthdate that encoded already” must prevent to be encoded again as it will be a duplication. Is it possible that two rows will and 2 columns will be the criteria? Thanks for the help
Catalin Bombea
Hi Michael,
Multiple columns can be used as a unique key, but not multiple rows, that is unusual. You should review the way you store information, a unique record should not be split into multiple rows.
Michael Cruz
Noted with this, Thanks!
Paygan
Hi Everyone.
Just looking to see, if anyone could help me under stand what this formula is and the meaning behind it =IF(D7>0,0.7,0) im new to IF functions and slowly learning.
ive notice a friend has used this IF Function =IF(D7>0,0.7,0) to calculate milage.
can anyone explain to me how it works, so im able to create one myself, but so i fully understand the meaning behind it.
Thank yous all
Mynda Treacy
Hi Paygan, in English that formula reads:
If the value in cell D7 is greater than 0, return 0.7, otherwise return 0.
Mynda
Stanley Prentice
=IFS(B15=””,D15=””,B15>0,J$10. Sorry, the previous had the wrong formula. This is the correct one however the rest of the previous post was correct. Does anyone have an answer?
Mynda Treacy
Hi Stanley,
The IFS function syntax is logical test, result, logical test, result…
Please see the IFS function tutorial. If you’re still stuck, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Stanley Prentice
=IFS(B15=””,D15=””)+OR(B15″”,D15=J6) This formula was written in cell J19. I want the results to be placed in cell D15 not in cell J19. the word in cell J6 is: Pension. I have other words in col. J that I may want to use depending on Dxx=Jxx. with xx being the cell number
Prem Aradhya
Could you suggest me a formula for scoring from 0 10, for the percentage of marks from 35 to 100.
Ex. For 35% the score is 0 and 100% the score is 10.
Mynda Treacy
Hi Prem, please use this VLOOKUP on a sorted list technique.
Naweed
Hi Guys,
can anybody help me out for below amount distribution?
if the bases is 2300 how to divide 100% on different 4 amounts
1. 2250
2. 1760
3. 1250
4. 1240
what amount percentage will be applied on each from 2300?
thank you
Mynda Treacy
Hi Naweed,
I’m not sure what you mean, sorry. Pease post your question on our Excel forum where you can also upload a sample file and we can help you further.
Galaxy Eduaction
Thanks for sharing this is aweesome.
Mynda Treacy
Our pleasure 🙂
kayane kumarasamy
Hi i have an excel project and really struggling with this question. No idea which function to use to label specific words: please see question below.
Use IF, data cleaning and / or lookup formulae to populate the columns for Type, Category and Detail.
>> Type: Codes beginning with “R” should be labelled “Revenue”. Codes beginning with “E” should be labelled “Expense”.
Exampes of codes ” EDA7001″ , RNC2001
>> Category: refer to the Lookups worksheet for the Categories.
>> Detail: refer to the Lookups worksheet for the Detail.
Philip Treacy
Hi Kayane,
You should post this kind of question in our forum along with the workbook that contains the data, because I can’t show you how to do the lookups without your data.
For the Type, you can use string functions:
but if there are only two Types you can use a single IF function for both
For the lookups you could use either VLOOKUP or XLOOKUP
Regards
Phil
James
Hi all,
I have 5 students – Adam, Oliver, George, Harry, Jack, Jacob – in cells A2, B2, C2, D2, E2, F2
and I have their grades 4, 4, 5, 6, 7, 8 – in cells A3, B3, C3, D3, E3, F3.
I am trying to figure out a formula for the following
1. To give me the lowest grade (i.e. 4) in cell G3 and the respective student’s name in cell H3.
2. To give me the 2nd lowest grade (i.e. 5) in cell I3 and the respective student’s name in cell J3.
Any help would be more than highly appreciated.
Mynda Treacy
Hi James,
You can use the SMALL function, to find the 2nd lowest grade and then use INDEX & MATCH to lookup the corresponding name, but it’s not clear what you want to do in the event of a tie. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Vic
Hey all. I am trying to figure out a formula for the following. I have 14 variables of data. If a cell equals a certain number, it will populate with that text. if not, it will show up as false. What I would like to do now, is if the range of X:X = false, look for other text and put that text into another cell. If its:
A1 False B1 text via formula needed in A5
A2 Text
A3 False
A4 False
A5 =formula for what i’m looking for.
I want it to look at the 4 cells and determine what text isn’t false and put it in a different cell. Something like If A1:A4=False, then B1=1 of 14 random texts.
Any help is appreciated! Thanks
Catalin Bombea
Hi Vic,
How can the formula decide which one of the 14 texts should be displayed? Can you describe all conditions (assuming that each of those 14 texts should be displayed when a specific condition is met)?
If there is no rule and one of 14 texts should be randomly displayed:
=IF(COUNTIF(A1:A4,”False”)=4,INDEX({“Date_GR”,”What_GR”,”Where_GR”,”What purchase_GR”,”Tip_GR”,”Sub_total_GR”,”Total_Cost_GR”,”Remarks”,”Where_GR”,”What purchase_GR”,”Tip_GR”,”Sub_total_GR”,”random text 1″,”random text 2″},RANDBETWEEN(1,14)),””)
Vic
Catalin, thank you for your response. I am only going to use 5 texts for example. Okay, I have the following table:
A B C D
1 Txt1 3 Txt3
2 Txt2
3 Txt3
4 Txt4
5 Txt5
When the data shows a number 1-5, it displays the corresponding text.
Since I have 5 values, I have a line for each option.
If the formula is false, it displays false.
Id like to find a formula that selects the range and selects the line automatically that does not read “False”, and displays that text in another cell
A B C D
MODEL# 1 1 Txt1
WANT NAME DISPLAYED HERE: XXXX 2 Txt2
=IF($B$1=$CF$1, “Txt1”) 3 Txt3
=IF($B$2=$CF$2, “Txt2”) 4 Txt4
=IF($B$3=$CF$3, “Txt3”) 5 Txt5
=IF($B$4=$CF$4, “Txt4”)
=IF($B$5=$CF$5, “Txt5”)
Now, I want to take B3:B7 and if #2 is the model number, B4 will read Txt2 and B3,B5,B6,&B7 will read FALSE. I want a formula to look through B3:B7 pick the text that isn’t “FALSE” and display that text in cell B2.
Does this make sense? Please let me know if you have any more questions. Thanks!
Catalin Bombea
Can you please upload a sample file with your data and the expected results? will be much easier to help you.
Use our forum to create a new topic and upload a sample file.
Vic
I tried registering, but when I went to log in, it will not let me.
Mynda Treacy
Hi Vic, please reach out via email (website at MyOnlineTrainingHub.com) so we can help you troubleshoot. What I can say here is the email address you used for this comment does not have an account set up in our system, so I suspect you haven’t registered properly. Mynda
Vic Trolio
I successfully registered and posted a sample file in the forum. Title of the topic is: IF and OR Function Help Needed.
Thanks!
anvitha
hey, I want a function to solve this problem
Customers who want to avail of an Early Bird discount must place their orders on weekdays between 11:45 a.m. – 12:00 p.m. (for lunch) or 6:45 p.m. – 7:30 p.m. (for dinner).
column D has delivery day(number) details and column E has delivery timings (12-hour format)
please help me solve this. thanks in advance
Catalin Bombea
Hi,
Can you please upload a sample file with an example of expected results? Will be much easier for us to understand your situation and help you.
Create a new topic on our forum after you sign up to upload the sample file.
Brian Coombs
Hi,
I am trying to do a formula involving multiple “IF” for the same cell, I have 4 possible answers for the same cell with different outcomes for each, so if (A1) is 0:051(or)0:151 0:2.51.
I have tried the nested ifs from the tutorials and the help in excel, and the “and” “or” but I just can’t seem to get it to give me an increase in the “value” cell…need help if you can please.
Brian Coombs
If A1 [is less than ?? in cell A2] then A3 “=” +3 or if A1 is greater than?? but less than?? [in cell A2] then A3 “=”+4….etc for all four variations
Philip Treacy
Hi Brian,
I’m afraid the problem isn’t quite clear. What kind of value is 0:2.51? Text? But you are trying to check if a value is greater or less than something so you must be working with numbers?
Also with this part if A1 is greater than?? but less than?? [in cell A2] if you have a value in A2 then you can’t check if something else is greater than and less than it. It can only be one or the other.
Please start a topic on our forum and attach the file you are working with so we can understand things better.
Regards
Phil
NAVAJITH.N
IF THE SALES IS LESS THAN RS.10001,THE COMMISSION IS 10% OTHERWISE YOU WILL GET 11% COMMISSION FOR THE AMOUNT ABOVE RS.10000. Please answer my question and give if formula.
Mynda Treacy
It’s difficult to give you a formula without cell references for your source data. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Flower
Hi… I am trying to work out a formula for a pricing excercise at work and am very stuck.
This is the information I have…
Base cost to service up to 3 fire extinguishers is $45
Cost to service any additional fire extinguisher is $4.05
Trying to set up a formula that shows this.
For example…
# of extinguishers Total Service cost
3 $40.00
4 $44.05 (base cost of $40.00 + 4.05 for the additional unit)
5 $48.10 (base cost of $40.00 + 8.10 for the additional units)
Are you able to help set this up?
Mynda Treacy
Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Ana
HELP :,(
3. The seniority bonus is calculated at the employment salary and is determined according to the seniority in work as follows:
pt. seniority 20 years – 25%
Mynda Treacy
This looks like a homework question, Ana. We don’t do homework, but if you have a go at writing the formula yourself then we can help you if you get stuck.
Philisha
Can you tell me the formula to use for:
If J1 is $10,000 or more put a “2” in cell B1
If J1 is less than $10,000 put a “1” in Cell B1
Thank you
Catalin Bombea
Hi Philisha,
Try this formula in cell B1:
=IF(J1>=10000,2,1)
John Lim
Hi all, Happy New year. can you help me on this, i want to display the monthly due dates automatically in each column. Example the contract is 6 months and the start date is Jan.31, 2021. and the due date is every 31st day of the month, So when i input 12 months, automatically the 12 columns will display the due dates of each month.
Column A – January 31, 2021
Column B – February28, 2021
Column C – March 31, 2021
Column D – April 30, 2021
Column E – May 31, 2021
Column F – June 30, 2021
Thank you
Catalin Bombea
Hi John,
Use this in the first cell:
=IF(Column(A1)>B1,””,EOMONTH(A1,Column(A1)))
Drag it to the right.
in A1 there should be the start date, in B1 the number of months.
John Lim
Hi everyone, i need help….
D5 = Tenant
E5 = monthly rental payment ( P 10,000.00)
F5 = +VAT 12%
G5 = – EWT 5%
H5 = PEZA accredited
i want the result that if the tenant is PEZA accredited there is no additional of 12% VAT
is it possible to add the current formula?
Current Formula =(E5+F5)-G5
thanks
Catalin Bombea
Hi John,
Instead of F5, put a condition:
=(E5+IF(H5=”PEZA accredited”,0,F5))-G5
John Lim
thank you Ms. Catalin Bombea
and also ask for your assistance on how can i monitor aging request.
Example i requested a report to be summited but i want to reflect that requested report is 3 days ago pending and also i want that the font text should be highlighted in red font color for the entire row
A5 = date request
B5 = Daily Time Record (Report status)
C5 = Remarks
thank you
Catalin Bombea
Hi John,
Can you please upload a sample file on our forum? will be much easier to help you on your real data structure.
Thank you
Priya
Hi, i need a help to findout the formula for the below condition.
here there are multiple columns, the first column will have the “A,B,C..etc..” and the 2nd column represent the value for each row. here if i enter the text in the “Text” column then the respective value should display in the Value column.
For Ex:
if i enter A then the value should be “1”
if i enter AB then the value should be “!2”
if i enter ACH then the value should be “138”
Heading1 Heading1 Text1 Value
A 1
B 2
C 3
D 4
E 5
kindly help!
Mynda Treacy
Hi Priya,
You should use a lookup table for the values and then you can use VLOOKUP or XLOOKUP (Microsoft 365) to return the value for the letter entered.
Mynda
Priya
Can you please help me with the query this would help me
Mynda Treacy
Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Priya
i couldnt upload the excel file. but this is the formula i used for one value,
=IFERROR(VLOOKUP(D4,$B$4:$C$13,2,FALSE),””)
Code Number Serial Rate
A 0 E 4 (i used the above formula)
B 1
C 2
D 3
E 4
F 5
G 6
H 7
I 8
J 9
if i choose “AA” then 00 should display, if i choose “BCA” 120 should display, if i choose “A” then 0 should display, if i choose “ABCDEFGHIJ” then 0123456789 should display. kindly help!
Mynda Treacy
Hi Priya, please get in touch via email with the error you get when uploading the file. I can’t help you here in the comments. The forum is the best place for support like this where an example file is required.
Mynda
sree
Hi
I need a formula in excel like, if the amount in C6 is 10000 and less the product should be 2.36, if it is 10001 and less than 100000 it should be 4.72, if it is 100001 and less than 200000 it should be 14.16.
Pls help
Catalin Bombea
Hi,
Try this one:
=INDEX({2.36,4.72,14.16,0},MATCH(A1,{0,10001,100001,200001},1))
Catalin
TRUSHAR
PLEASE HELP ME TO SET FORMULA WHERE ONE CELL VALUE IS FIXED SAY 1000 AND I WANT TO SET FORMULA WHICH WILL CALCULATE AUTOMATICALY RANGE IN OTHER 2 CELL FRM FROM THE BASE PRICE 15%-20% & 25% TO 33%. EXAMPLE: 1000(B1 CELL VALUE) CALCULATE RESULT IN C1 CELL (1150-1200) AND IN D1 VALUE (1250-1330).
Philip Treacy
Hiya,
Sorry the problem isn’t clear. Can you please start a topic on our forum and attach some sample data with an example of your expected results.
Regards
Phil
sabeen
ITEM NO DESCRIPTION QTY UNIT PRICE DISCOUNTED UNIT PRICE
5101 330ML (40 PC BOX) 300 13.00 9.50
=IF(C21=0,”0.00″,IF(AND(C2149,C21100,C21<301),"9.50",9))))
2 criteria checking.
i want one extra criteria also check it,
1) if ITEM=5102 and if QTY
(C21=0,"0.00",IF(AND(C2149,C21100,C21<301),"9.50",9))))
2)if ITEM=5100 and if QTY
(C21=0,"0.00",IF(AND(C2149,C21100,C21<301),"2.50",2))))
Mynda Treacy
Hi Sabeen,
Aside from adding the extra criteria, there are some issues with your formula that you may not be aware of. i.e.
1. =IF(C21=0,”0.00″ this will return text in the form of 0.00 not a zero. I presume you actually want a zero. Likewise for “9.50” should be 9.50 and “2.50” should be 2.50.
2. AND(C2149,C21100,C21<301), is only testing if C21 is less than 301. C2149 and C21100 have no logical test. I presume you want to test all 3 cells are < 301.
Assuming the above, then you can use this formula:
=IF(C21=0,0,IF(AND(cell containing the item=5102,C2149<301,C21100<301,C21<301),9.50,IF(C21=0,0,IF(AND(cell containing the item=5100,C2149<301,C21100<301,C21<301),2.50,9))))
Mynda
Jem
Hi there, Please help me with the correct formula for this question;
If the invoice is paid in full as of September 30,2020, display Yes; otherwise display NO
Mynda Treacy
Hi Jem, please post your question on our Excel forum where you can also upload a sample file and we can help you further. Mynda
Shaylen
Hi there im struggling with a question in an excel assignment, so it goes like this.
“The delivery date should be within 10 days from today expect if the invoice is created in December, then we need 21 days time, hint use an IF statement”
I’ve tried doing the IF statement but it’s just not working, I’ve done.
=IF(TODAY()=”December”, Today()+21,Today()+10)
It does add on 10 days since we not in December, but if i change today’s date to December it still adds on 10 days
Mynda Treacy
Hi Shaylen, the question isn’t clear to me, but looking at your formula, TODAY returns a date, not a month name. If you want to check if the month is the 12th, you’d do this:
If you’re still stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
Sayali
hi I need help for below formula,need to set the “GTS IS….” part mapped as per the E54 col.input
=IF(AND(OR(E54=”100″,E54=”101″,E54=”104″,E54=”105″,E54)),” GTS IS:New logo”, IF(AND(OR(E54=”102″,E54=”103″,E54=”106″,E54=”107″,E54=”108″,E54=”109″,E54)),”GTS IS:Project /RFS” , IF(AND(OR(E54=”213″,E54=”214″,E54=”215″,E54=”217″,E54=”218″,E54=”222″,E54=”223″,E54=”301″,E54=”302″,E54=”303″,E54=”300″,E54)),”GTS IS:Other”,IF(AND(OR(H54=”211″,E54),(H54=”216″,E54)),”GTS IS:Skills for Value”,”GTS IS:Center to Center”))))
Mynda Treacy
Hi Sayali,
I think it would be better to use VLOOKUP or XLOOKUP if you have Microsoft 365/Office 365. You can have a list of the categories (GTS IS:New logo etc.) in a column beside the numbers they match. See links for more detailed tutorials. If you’re still stuck, please post your question and a sample Excel file on our forum where we can help you further.
Mynda
So
hi
i need a help with avarege data row , that gived number 1-4 and 1 is 50%, 2 is 75%, 3 is 85%, 4 is 100%
=AVERAGE(H4:AG4,(IF(AND(H4:AG4=4),100,IF(AND(H4:AG4=3),85,IF(AND(H4:AG4=2),75,IF(AND(H4:AG4=1),50))))))
Mynda Treacy
Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Terri P
I need help with adding a formula to an existing MOD formula in a timesheet. I have in column B, Time IN, in column C is Time OUT and column D is Project Hours. The formula I have in cell D2 is =MOD(C2-B2,1). Cells B2 & C2 are formatted as h:mm AM/PM and cell D2 is formatted as h:mm.
If I type 9:00 am in cell B2, and leave cell C2 blank, it shows 15:00 in cell D2 for the Project Hours. I know this is because the formula is based on time and it is showing how many hours between 9:00 am and 12:00 midnight. We have some employees who clock in at 11:00 pm and will clock out after midnight, which is why I have the MOD formula.
Eventually, the employee will type in their Time OUT in cell C2, but until they do so, I don’t want D2 to show 15:00 hours, instead I would like for cell D2 to be blank until cell C2 is typed in. Is there a formula for this that can be included with the existing MOD formula or even a conditional format set?
Thank you.
Mynda Treacy
Hi Terri,
=IF(C2=””,””,MOD(C2-B2,1))
Mynda
Terri P
Thank you so much Mynda, that worked!! Have a great day!
Terri
Jonathan A. Del Rosario
Please help me make a formula for my data, scenario is this:
Where column B2 is the Release Date and column B5 is the 1st Amort Date.
f the release date (B2) Range from 1 – 25 of the month the 1st Amort Date (B5) should be the 10th of the following Month,
then if the release date (B2) Range from 26 – 31 of the month the 1st Amort Date (B5) should be the 5th of the following Next Month.
Example: B2 – July 15, 2020
B5 – August 10, 2020
then
B2 – July 26, 2020
B5 – September 5, 2020
Mynda Treacy
Hi Jonathan,
Try this:
=IF(DAY(B2)<26,DATE(YEAR(B2),MONTH(B2)+1,10),DATE(YEAR(B2),MONTH(B2)+1,5))
Mynda
Jonathan A. Del Rosario
Thanks for the help..I will try it..
Jonathan A. Del Rosario
Thank you very much Mynda!! Just try it and got it exactly what I want.
Mynda Treacy
Glad I could help 🙂
Dev P
Hi,
I’m trying to calculate Surcharge where condition should be like this. If C18 is > 50,000,000 then surcharge is 10%, if C18 is >10,000,000 then surcharge is 20%, if C18 is >20,000,000 the surcharge is 30%. But unfortunately, when calculating surcharge value is still 10% even if this amount is more than 10,000,000
Rounded Total taxable income (C18) 10,000,000.00
Total Tax (C20) 2,812,500.00 =ROUND(IF(C18>1000000,(C18-1000000)*30%+112500,IF(C18>500000,(C18-500000)*20%+12500,IF(C18>250000,(C18-250000)*5%,0))),)
Surcharge (C21) 281,250.00 =IF(C18>=5000000,C20*10%,IF(C18>=10000000,C20*15%,IF(C18>=20000000,C20*25%,1)))
Please help.
Philip Treacy
Hi Dev,
IF stops evaluating when it gets a True result and your first test says is C18 >= 5 000 000, which it is. So you get C20*10%.
You need to reorder the tests like this
=IF(C18>=20000000,C20*25%,IF(C18>=10000000,C20*15%,IF(C18>=5000000,C20*10%,1)))
Regards
Phil
Dev P
Hi Phil,
Working perfectly. Thanks a lot. 🙂
Philip Treacy
no worries.
Liz M
I’m trying to work out the costs of a booking depending on hours booked against different prices. I’m not sure about the IFs function and tried nested Ifs but I can’t get it to work. I need the formula to count three time ranges and then multiply the count by a price. Thank you in advance.
=IFs((F9:I9=”8.30 – 15.30″,COUNTIF(F9:I9,”8.30 – 15.30″)*28),+ (IF(F9:I9=”8.00 – 17.00″),COUNTIF(F9:I9,”8.00 – 17.00″)*35) + (If(F9:I9=”8.00 – 18.00″,countif(F9:I9,”8.00 – 18.00″)*38.5)))
Philip Treacy
Hi Liz,
Try this
=COUNTIF(F9:I9,"8.30 - 15.30")*28 + COUNTIF(F9:I9,"8.00 - 17.00")*35 + COUNTIF(F9:I9,"8.00 - 18.00")*38.5
Regards
Phil
Liz
Thank you, that’s brilliant and it totally worked.
Philip Treacy
You’re welcome.
Sumit Singh Chaudhary
We would like to see which of our orders are generating the most revenue. If an order has an Order Total of more than $1,000, we will call this a “Star” order.
Populate column AG: Star Order Proposal A with the value “Star” if the Order Total is more than $1,000. If it is not, the cell should simply remain blank. How many “Star” orders are there?
Philip Treacy
Hi Sumit,
Without your data it makes it difficult to write the formulae for you. You can post a topic on our forum and attach your file.
However, you could try
=IF(A1>1000,”Star”,””)
You should consider using Conditional Formatting to visually indicate your Star orders by, for example, filling the cell with a colour.
In order to count how many Star orders you have, you can use COUNTA e.g.
=COUNTA(A1:A5)
Regards
Phil
suzu
how to make formula in this condition after 8:30 and before 16:30, fill the cell color with red
Catalin Bombea
You should use:
=AND($B2>=8.5/24, $B2<=16.5/24) Adjust the starting cell range for conditional formatting as needed. I assume you have time values in the range.
mohammed ab Sheriff
A B C D E F
1 FIRST NAME SURNAME POSITION FULL NAME AGE ANNUAL SALARY
2 JOHN SMITH MANAGER 35 85
3 JIMMY MICHAELS SALESPERSON 26 60
4 MICHELLE LEVINE RECEPTIONIST 37 55
5 LEVI JOHNSON TECHNICIAN 40 75
6 JENNA MASTERSON PROGRAMMER 32 100
IF Age of a worker is >= 35, then ALLOWANCE should be 15% of his/her age, else allowance should be 9% of his or her age
Mynda Treacy
Hi Mohammed,
This is a simple IF formula e.g.:
=IF(age>=35,age*.15,age*.09)
Mynda
Tonyo Godinho
If J3>K3 Sell and If J3<K3 Buy
Mynda Treacy
Hi Tonyo,
=IF(J3>K3, “Sell”, “Buy”)
Mynda
Hlobsile
Hello
Can you please help me, I don’t understand more about AND and OR. can you please explain and justify this for me: =AND(E2=”Married”,F2=1)*1
Mynda Treacy
The AND part of this formula: =AND(E2=”Married”,F2=1) returns a TRUE or FALSE. When you perform a math operation on TRUE or FALSE, like in your example, *1, it converts TRUE and FALSE to their numeric equivalents of 1 and 0 respectively, so the result of the formula will always be 0 or 1, depending on whether both logical tests are TRUE or not. I hope that helps.
Mynda
jennifer michaels
Hi, i know could you help me understand what this says? also, direct me a page that may explain max & mins? this page was very helpful for the If statements.
MAX(0,MIN((B9-IF(B10=”Y”,B5,0))*B13,B7))
thank you!
Catalin Bombea
Hi Jennifer,
Without context and real practical use, they mean nothing.
There are 2 parts, MIN is used here to put a limitation, the result should not be higher than the limit set in B7, BUT can be lower : MIN( (B9-IF(B10=”Y”,B5,0))*B13, B7)
Then, the result has another condition applied: the result of the MIN function should not be negative, in this case MAX(0,negative number) will always be 0. If MIN returns a positive value, it will obviously be the value returns by MAX calculation.
=MAX(0, resultFrom MIN function )
Grace
I am not very savvy on Excel and I am trying to figure out a macro that is in my spreadsheet. =IF(D2=””,””,IF(D2=E2,”Pass”,”Check”))
Mynda Treacy
Hi Grace,
In English, your formula reads:
If D2 is empty, then return nothing, if the value in D2 is the same as the value in E2, then enter the text “Pass”, otherwise enter the text “Check”.
I hope that helps.
Mynda
Kuba
Hello, dear pros!
I need to calculate the following:
I am paid 25 cents for the first 150 miles. And if I drive more than 150 the difference is multiplied by 40 cents. What the formula would be?
Thank you for your help in advance!
Catalin Bombea
Ok, so for the first 150 miles your get 25 cents, if you have 190 miles, you get 25 cents for the first 150 and 40 cents for the rest of 40 miles?
Try:
=IF(A1<150,A1*25,150*25+(A1-150)*40)
Bharath
Please help. I need your help to correct the below formula. .
=IF(AND(G1>=DATE(2019,12,15),J1=”Listed”),”Yes”,”No”)&IF(AND(G2>=DATE(2019,12,31),J2=”Listed”),”Yes”,”No”)
basically i want
G1>=DATE(2019,12,15),J1=”Listed” to be “Yes”or G2>=DATE(2019,12,31),J2=”Listed” to be “Yes” if not “No”
I just need answer to be one “Yes” or “No”. At the movement I am getting 2 answers like “YesYes” or YesNo” etc because of the & symbol I used in the formula and I am not sure what to use instead of & or if I can use a different formula.
Philip Treacy
Hi Bharath,
I think this is what you need
=IF(OR(AND(G1>=DATE(2019,12,15),J1="Listed"),AND(G2>=DATE(2019,12,31),J2="Listed")),"Yes","No")
Regards
Phil
Bharath
Thanks Philip
I tried this formula earlier but it does not work.
what i want is
if a date is greater than or equal to 15 December 2019 and if it is Listed the Yes if not No
i also need to add a formula in addition to the above
if a date is greater than or equal to 15 March 2020 then Yes if not No
Philip Treacy
Hi Bharath,
Without seeing your data and examples of the values in the cells, along with the expected results you want for those values, it’s too easy to misunderstand the the problem when explained merely in words.
For example, what exactly do you mean by ‘I also need to add a formula’? How exactly? How does the 2nd formula interact with the first one?
Please start a topic on our forum and attach your workbook with sample data and expected results from that data.
Regards
Phil
Siyabonga
Hi There,
Quick question is it possible to have the (And,Or,IF) in one function and where logical tests are made up of different small referencing formulas.
Mynda Treacy
Yes, it’s possible!
Corr
This if what I could come up with….=IF(Inverter!D$19=1;Inverter!$C3;IF(Inverter!D$19=2;Inverter!$D3;IF(Inverter!D$19=3;Inverter!$E3;IF(Inverter!D$19=4;Inverter!$F3;IF(Inverter!D$19=5;Inverter!$G3;IF(Inverter!D$19=6;Inverter!$H3;IF(Inverter!D$19=7;Inverter!$I3;IF(Inverter!D$19=8;Inverter!$J3;””)))))))), but the problem is that I need 15 IF statements and I am sure you can get the sequence. Is there a different formula to do the same. So I want to check cell D19 what ever the number in that cell return the same value in that numbers column.
Catalin Bombea
Hi Corr,
Try:
=INDEX(Inverter!$C3:$J3,MATCH(Inverter!D$19,Inverter!$C2:$J2,0))
The formula will search the D19 value into Inverter!$C2:$J2 range and will return the values from Inverter!$C3:$J3 range, you will have to organize your data this way.
Tomáš Martínek
hello, i want do a teble where i can see % of income foreach hour that is shop that i work in open. I would liek to do it like from 10:00 to 10:59 was sold stuf worth 75€ and from 11:00 to 11:59 25€ so from 10:00 to 10:59 my shop get 75% of income. i want to run in for like 1 month of days and i don´t want to do it manualy is there a way to make a function for it? if so can you help me how to make it? I think it should be someone think like— found everywhere IF date from 10:00:00 to 10:59:00 them get sum of 5 to left on x. … but how to write it in code … 😀
Catalin Bombea
Hi,
If you have store data, you don’t need formulas, a simple pivot table report will do.
Can you upload sample data on our forum? This way we will be able to give you a functional solution.
Catalin
Smita
In excel using formula/function If any two months sale is less than 70% out of 3 months result should be “More work” otherwise blank. Even one month sale is less than 70% it should be also blank. Please help to solve.
Mynda Treacy
Hi Smita,
The formula will depend on the structure of your data. Please post your question and sample Excel file on our forum where we can help you further.
Mynda
Freddie romero
If average is greater than 100 then show the date today else add 3 on today’s date
Mynda Treacy
Hi Freddie,
Mynda
Minhas Mehmood
I need help please.
if(a1>0,”1″,if(a2>0,”2″,if(A3>0,”3″,”0″)))
I have 3 column and I need that if one column having value greater than 0 than its shows 1, if two column have values greater than 0 than shows 2 ….and so on…
Can anyone help me please in this?
Philip Treacy
Hi Minhas,
Use COUNTIF
=COUNTIF(A1:A3,”>0″)
Regards
Phil
Catalin Bombea
A COUNTIF should give you the count: =COUNTIF(A1:A3,”>0″)
Saif
if j5>0 then j5*g7,if j6>0 then j6*h7, if j5 & j6>0 then j5*g7+j6*h7
L.E.: Please help formula for this equation
Catalin Bombea
=IF(J5>0,J5*G7,0)+IF(J6>0,J6*H7,0)
Faisal Shahzad
Hi
can you help me to find a value
if A1,B1,C1,D1,E1 have values like that
0, 0, 2, 0, 0
How we found this value
C1 value not specified its Formula based value
Regards
Catalin Bombea
Hi Faisal,
Only one of the sample cells can have a value greater than zero? Or there can be more than one?
Please provide more details about what is needed, if more than one cells is greater than zero, which one should be returned, the first value, the last?
Nikil
Please my formula
IF(((G4-F4=0, “+” & TEXT(ABS(G4-F4),”hh:mm”)))
Catalin Bombea
=IF(G4-F4=0, "what should happen if result of logical test is zero?", "+" & TEXT(ABS(G4-F4),"hh:mm"))
Megan L Funneman
I’m trying to do a formula so that if B1= “s”, then P7. If B1=”m”, then Q7. If B1=”h”, then R7. I’m able to get a formula for just two of the possibilities, but when I add the third option, I’m retrieving an error.
Mynda Treacy
Hi Megan,
Try:
Mynda
LISA HAYS
In cell B8, enter an IFS function to return 0 earned if the Total Lessons in cell B6 is less than 5, return 1 earned if the Total Lessons is less than 10, otherwise return 2 earned.
Hint: Type TRUE as your logical_test3 argument so that the function returns 2 earned if the other logical tests are false.
Totally lost on this one problem involving multiple work sheets in workbook. Keep getting an error
Mynda Treacy
Hi Lisa,
This sounds like an exam question, so I don’t want to solve it for you, but here is a tutorial on the IFS function, which they’re asking you to use. Hopefully that will point you in the right direction. If you’re still stuck, please post your question on our forum where we can help you further.
Mynda
Gerhard Bester
Good day.
I have a start date and a estimated end date. (normally 3 years plus from actual start date). I have an additional cell that needs to have 1st, 2nd and 3rd which is supposed to update automatically linked to a today date.
=IFS(TODAY()>=EDATE(L2;24);”3rd”;TODAY()=EDATE(L2;12);”2nd”;TRUE;”1st”)
Actual Starting Date Estimated Completion Date (Trade Test) Progression (1st / 2nd / 3rd)
2019/01/07 2021/01/07 1st
The problem is that the formula does not calculate the 2nd.
Actual Starting Date Estimated Completion Date (Trade Test) Progression (1st / 2nd / 3rd)
2019/01/07 2021/01/07 1st should have been a 2nd year based on formula. Anniversary date2020/02/07 = 2nd and 2021/02/07 = 3rd
Catalin Bombea
Hi Gerhard,
The order of the arguments is important. Also, EDATE returns a date that is exactly 12 or 24 months after start date, so EDATE(DATEVALUE(“2019/01/07”),12) returns 2020/01/07, if you compare it with TODAY(), only in a very specific start day-today combination the logical test will be true, I guess you have to review the logical test as well.
CLAIRE BROWN
Hi I’ve done this before but cant remember the correct formula…
I’m looking at getting an answer to a sum depending on if a number is 25 or 50. Ie if 50 then the sum be needs to be multiplied by 2.
I am using this formula but i just get the answer saying FALSE when i actually need to the figure in numbers.
=IF(D43=”25″,(G43/C43),IF(D43=”50″,((G43/C43)*2)))
Mynda Treacy
Hi Claire,
When your criteria is numbers you don’t use double quotes. Double quotes are only for text. Try:
=IF(D43=25,(G43/C43),IF(D43=50,((G43/C43)*2)))
Mynda
Mandy
Hi, I am trying to do a formula but I think I am missing something or have it completely wrong. What I need to do is determine the data in cell A2 based on what I type in A1. e.g
If if type “Blue” into cell A1 then I want cell A2 to say “Green” but if I type “Red” into cell A1 then I want cell A2 to say “Orange”, but if A1 is blank, I want A2 to be blank too.
I have tried doing an IF and an IF/OR formula but it still doesn’t seem to work. Any help you could give would be gratefully appreciated.
The formula I had is =IF(A1=Blue,Green,)IF=(A1=Red,Orange,),” “)))
Thank you
Mandy
Philip Treacy
Hi Mandy,
Try this
=IF(A1="Blue","Green",IF(A1="Red","Orange",""))
Regards
Phil
Mabel
I have a checklist that must be checked with an “x” for Excellent, Satisfactory or Needs Improvement columns. Each Row has categories that need to be judged. The categories range from 2 to 6 per section. I want the overall judgment row to automatically show “x” when there are four or three let’s say excellent judgment in those rows with four. If there are six categories, if there are six, five or four x, the x automatically shows in the overall, and so on. I have been trying to use the If formula but cannot figure it out. Thank you.
Mynda Treacy
Hi Mabel,
We’d love to help, but it’s a bit difficult to picture your data. If you post your question on our Excel forum you can also upload a sample Excel file and we can help you further.
Mynda
Jason Mack
I’m having an issue with the following formula.
=if(OR(E2=”Jason”,”June”),D2,D2/2)
I want to display the value in D2 if E2 is either ‘Jason’ or ‘June’ and if anything else(not ‘Jason’ or ‘June’) divide D2 by 2.
Any help would be welcomed! Thanks!
Here is the error produced:
Error
OR expects boolean values. But ‘June’ is a text and cannot be coerced to a boolean.
Catalin Bombea
Hi Jason,
You’re close:
=if(OR(E2=”Jason”,E2=”June”),D2,D2/2)
Desmond
=IF(E17>1277777.78,23000,(1.8/100)*E17):IF(E17<138,888.89,2500)
Can someone says what wrong with this statement
Catalin Bombea
Before the second IF you have a colon: “:IF”
What are you trying to do?
“:” is a range operator, works with ranges, not values. A1:A10 will work, as it’s a valid range
Robin das
facing a simple problem. I want to put a formula if cell a6=p then I need 500 to be on a cell, and if cell a6=a then I need 0 or blank on the same cell.
Mynda Treacy
Hi Robin,
Try:
Mynda
LeAnn
Good Evening,
I have a graded vesting table and I need to have a formula for the following:
Less than 2 years = 0
2 years but less than 3 = 20%
3 years but less than 4 = 40%
4 years but less than 5 = 60%
5 years but less than 6 = 80%
6 years and thereafter = 100%
I’ve tried some IF statements but cannot get it to work correctly. Any help would be appreciated.
Thanks!
Catalin Bombea
Hi LeAnn,
Try:
=INDEX({0,0.2,0.4,0.6,0.8,1},MATCH(A1,{0,2,3,4,5,6},1))
Mynda Treacy
Hi LeAnn,
You should use a sorted VLOOKUP formula for this, as explained here: VLOOKUP Sorted List Explained
Mynda
Faisal
Hi,
Please help me to correct my farmula for incentive sheet
=IF(F5105%,H10, )
The formula shown Result is #Value!
Philip Treacy
Hi Faisal,
I don’t know what you want to do with the formula so I’m guessing to give you an answer.
It looks like you are testing the value if F5 to see if it is 105% of something.
If this is true then you’ve written that the answer will be the value in H10. You then have a comma but haven’t specified an answer if F5 is not 105% of something.
Really, everything you need to fix this is already in this article.
But, if you have a value in A1 then you could write this
=IF(F5=A1*1.05,H10)
which omits a value if the test is false, in which case the answer will be the Boolean value False.
You could also explicitly specify a value for the false result like this
=IF(F5=A1*1.05,H10,A2)
Regards
Phil
Faisal
Thanks for Reply Phil
Actually i am making a Incentive Calculation Sheet for our employees
and i am stuck off and need your help
Example
if D2 is = to 100%, 101%,102%,103%,104%,105%
then shown the unit that is calculating in slab 100%. 101%,102%,103%,104%,105%
Regards
Philip Treacy
Sorry Faisal still not clear.
Is D2 100% of what? What am I testing D2 against?
Also not sure what you mean by ‘shown the unit that is calculating in slab …’
Please start a forum topic and supply a workbook – that will make things easier to understand.
Regards
Phil
Mynda Treacy
Hi Faisal,
Cell E6: =IF(C6>=100,100,0)
Cell F6: =IF(C6>=105,5,0)
Cell G6: =IF(C6>105,C6-105,0)
In future, please post your questions on our Excel forum where you can upload your sample file and we can help you more promptly.
Mynda
Philip Treacy
Hi Faisal,
Cell E6: =IF(C6>=100,100,0)
Cell F6: =IF(C6>=105,5,0)
Cell G6: =IF(C6>105,C6-105,0)
In future, please post your questions on our Excel forum where you can upload your sample file and we can help you more promptly.
Phil
Amol Gharpure
I want to set a excel formula as follows:
I want assign 0 marks for persons scoring up to 74.99% and from 75 to 100 % marks scored, assign 20 marks on pro-rata basis.
Kindly suggest the formula.
Catalin Bombea
Hi Amol,
Try this:
=INDEX({0,20},MATCH(A1,{0,75},1))
U T
Kindly suggest formula to find pass or fail in different cell as c2, f2, h2, k2, n2, and q2 but need to pass in 4 subjects out of 6 and passing marks is 40.
Catalin Bombea
Hi,
It’s best if you can upload a sample file with your data structure and details about what you are trying to achieve, hard to visualize what you have in those cells you mentioned.
Simply create a new topic after signing up to our forum, you will be able to upload there.
UT
I don’t understand you reply only in indirect way that pretend you cannot give answer
Mynda Treacy
Without seeing your data it is too hard to write an answer that will be accurate. You will then reply asking for modifications so if we can see your data all of that can be avoided.
So, if you can start a topic on the forum and attach your workbook that will help us help you.
We also need a clear explanation of what the inputs are and what the expected outputs are. As it is what you have already stated is not clear enough.
Regards
Phil
Ali Rehman
Hi,
I want to calculate the sum of the cells if in column B have text “paid” then calculate values from Column A in respective cell and if Column B have text “Un-Paid” then the values from Column A will not calculated… reference list is below…
Column A Column B
Rs 3,000.00 Paid
Rs 30.00 Paid
Rs 50.00 Paid
Rs 5,000.00 Paid
Rs 10,000.00 Un-Paid
Rs 10,000.00 Un-Paid
Rs 5,000.00 Un-Paid
Rs 330.00 Un-Paid
Rs 650.00 Paid
Rs 785.00 Un-Paid
Looking forward for prompt response please…
Thanks
Mynda Treacy
Hi Ali,
You need a SUMIF formula for this.
Mynda
Ali Rehman
Mynda kindly describe the SUMIF formula in details..
i.e write the formula for my task please….
Thanks
Catalin Bombea
Hi Ali,
You have here an article about SUMIF.
Joseph P K
i need a help if i put above 1 in i4, it will be reflected in i5 the value of i4*8-8, that means if i4 is 1.25 the value of i5 is 2 (1.25*8-8)
how i given if formula
pl. help
Catalin Bombea
Hi Joseph,
In cell I5 you should have this formula:
=I4*8-8
BHUSHAN CHHAGAN LANJEWAR
I need help to put formula in excel. If 1-50 then rate is 9.85, if 51-100 then rate is 9.55, if above 100 then rate is 9.10. Please calculate the formula for excel sheet.
Mynda Treacy
Hi Bhushan,
Where the value you’re checking is in cell A1.
Mynda
Catalin Bombea
Try a simple index match version:
=INDEX({0,9.85,9.55,9.1},MATCH(A1,{0,1,50,100},1))
Vix
A. B. C. D. E.
1. 20. 30. 40 50. 60.
2. 59. 62. 79. 80. 81
3. 78. 98. 108. 118. 128
If someone has a salary let’s say 30(B1) and our promotion process is 2 steps front {50}and the move one grade down to closest number 59(a2).
L.E.:
Sorry the question was what formula would help.
Catalin Bombea
Hi Vix,
Please upload on our forum a sample file with a few manual examples, it will be easier to understand the situation and provide a functional solution.
Catalin
Andy
B2*C2=E2 then E2+B2=F2 then F2=B3
18,185.78 X 12.8% = 2,327.78
18,185.78 + 2,327.78 = 20,923.83
20,923.83……..
all im trying to do is have the excel automatically track the financial interest, where all I have to do is change the interest of the given year.
I’m still green behind the ears.
Thank you
Andy
Mynda Treacy
Hi Andy,
Thanks for your question. Can you please post it on our Excel forum where you can upload a sample Excel file to explain your requirements as I’m not following the explanation here, sorry.
Mynda
deena
i need help with this question {Write a formula for Gross Pay column for each employee as 40*(Rate per Hour) +(Hours Worked-40)*1.5*Rate per Hour if Hours Worked is larger than 40 and Rate per Hour*Hours Worked if Hours Worked is less than 40} and thank you.
Catalin Bombea
Try:
=IF(A1-40>=0, Rate*(40+(A1-40)*1.5), Rate*A1)
Sherry Roark
I am trying to figure out the formula for : IF function with a nested AND function to test for lunch profits (in cell B2) greater than or equal to $100 (Cell E2), that returns the work BEST when both conditions are met. I have gotten this far but then I am lost .. IF(AND(B2=”lunch”,E2=>=100), “BEST” but this is not working please help !
Thank you so much for your assistance. !
Catalin Bombea
Hi Sherry,
You can try:
=IF(AND(B2="lunch",E2>=100), "BEST","not best")
garry edmunds
Found the problem and it works great! Thanks Mynda you are the best!!
I realized I need a new step in the formula and wonder if it may be possible to do.
with the same formula and if A1 was cat could I add the number in E1, and if A1 was dog could I subtract the number in E1?
Mynda Treacy
Yes, just modify the formula in the ‘value if true’ argument.
Garry
thanks for the quick reply
so would the formula would be =if(A1=”cat”,C1-D1+E1,if(A1=”dog”,D1-C1-E1,0))
or would it be different?
Mynda Treacy
Probably. If it doesn’t give you the result you want, then modify the formula. You can always create the formula in another cell and when it’s working as desired, copy that formula into the value_if_true argument of the IF formula.
Garry
I have been racking my brain on how to write a formula for this.
in cell F:1 if A:1 = “cat” then sum C:1- D:1, or if A:1 = “dog” then sum D:1-C:1
This is probably a simple formula but I am having a hard time figuring it out.
Thanks
Mynda Treacy
Hi Garry,
Mynda
Garry
Thanks for responding, but when I use that formula I get an error that says I have entered too many arguments for this function and doesn’t give me the results I need.
Garry
also I don’t want a value of 0 returned if false, i wanted the sum of if first statement is met to be c1-d1, or if second statement is met the sum d1-c1, i don’t need the word “false” to show up in the cell
Mynda Treacy
Sorry, Garry. Parenthesis in the wrong place. Try this:
=IF(A1=”Cat”,C1-D1,IF(A1=”Dog”,D1-C1,0))
Replace the zero with whatever you want to show up in the case that cell A1 doesn’t contain Cat or Dog.
Mynda
Garry
Thanks for the correction, it got rid of the error but it still isn’t giving me what I need. f1 just gives me a 0 no matter what is in A1.
I am probably not explaining it clearly so I will try to explain it one more time. In the cell f1 I would like a calculated numerical value if A1 has the word cat in the cell I would like the sum for c1-d1 but if A1 has the word dog in the cell I would like the sum for d1-c1.
Example: A1 will either have the word cat or dog in it and nothing else, so if c1 has the number 50 and d1 has the number 45 I would need: if cat then subtract 50 – 45 = 5 or if dog subtract 45 – 50 = -5
The formula as written, regardless if it has cat or dog in A1 always comes back 0 which isn’t correct.
I hope this makes sense. Thanks
Mynda Treacy
I tested the formula using the criteria you provided and it works for me. I wonder if cell A1 doesn’t just contain the word ‘Dog’ or ‘Cat’ but perhaps this word is one of many text characters in the cell. Have you checked to make sure there isn’t a space after Dog or Cat e.g. ‘Dog ‘ These spaces are not visible on the face of the cell. You need to edit it and see where the cursor stops. If that’s not it, please post your question on our Excel forum where you can upload a sample Excel file and we can troubleshoot further.
Mynda
Mudita Nangia
Hi,
I want to excel for the training program, where I have around 90 employees who need to complete 4 training. this training need to be refreshed every two year. so for every year I want the percentage complete and not complete data, this data should also include the employee who need the refresher course and whether they completed or not.
Can you help me with the formula.
Catalin Bombea
Can you please upload a sample file on our forum?
Mudita Nangi
How can I upload the file. I tried but cannot see any option for uploading.
Mynda Treacy
Hi Mudita,
On the forum, when you create a new topic there is a button under the section where you type your question for ‘Attachments’.
Mynda
Mudita Nangia
I have uploaded the sample report under the post “Training Record – Query”, can somebody please reply. It is urgent.
L.E.:
Forum: General Excel Questions & Answers
Topic: Training Record – Query
Avatar Mudita Nangia – 17 hours ago
Here is where I posted the query with sample report
Connie Jensen
If Sheet Jan 18 cell A1 is >135 result should be FT if Sheet Jan 18 A1 is <136 result should be PTE if Sheet Jan 18 A1 is 0 result should be PT
Is this formula possible?
Thank You
Mynda Treacy
Hi Connie,
On sheet Jan 18 enter this formula:
Mynda
Hazeez-ur-Rehman
I am making result I wanted to do if a student fails in 1 subject then excel shows supply if he fails in 2 subjects then excel shows 2 supply but if he fail in 3 or more than 3 subjects then excel shows it Fails Whats the formula I can apply?
Catalin Bombea
How is your data organized? You just have the number of passed exams in a column, or each exam has a separate column?
Please use our forum to upload a sample file, it will be easier to help you.
Catalin
Jalen
Hello, Im working with alot of data trying to find a formula that will help me with it. Myt example is that if columns B and C match H and I then use the information in J and K to fil in columns D and E.
Any assistance would be appreciated. Thank you
Philip Treacy
Hi Jalen,
You’ll need to provide more specifics for us to give you a good answer. What type of data is stored in Cols B and C? Are we checking for a date, a string, a number?
How exactly do you want to use the info in Cols J and K?
If you open a topic on the forum and supply your workbook with data it’ll be easier to give you some help.
Regards
Phil
hailey
How do I enter an IF,OR function to find out if D2 is greater than or less than 2 or G2 is “yes” or “no”
Philip Treacy
Hi Hailey,
Did you give t a try yourself?
Here’s the formula
=IF(OR(D2<>2,G2=”yes”,G2=”no”),TRUE,FALSE)
So if D2 is not equal to 2 or G2 contains either yes or no, you’ll get the result TRUE, otherwise the result is FALSE.
Regards
Phil
Sol Wize
Hi,
I’m trying to fill a cell value based on whether another cell has any value whatsoever. So if value of Cell A2 is ABC, Cell E2 should have value 1.
I use the value in E2 to sum up the total.
Now the problem I face is that although I’m getting the Value 1 in E2 using IF function. The data is not considered numeric & the sum is always zero.
But if I manually enter 1 in each cell, it is ok. Kindly help.
I’m using the below formula:
=IF(([@BARCODE]>1),”1″)
Here barcode is the name of the field.
Catalin Bombea
Hi Sol,
Whatever you put between double quotes will be text.
Try:
=IF(([@BARCODE]>1),1)
Fazal
Need help urgently please
I am using – formula like
=H8-i8-f8
Now i want to write in the answer that if the answer of above formula is in “-” then type 0. And if answer in positive then write the answer….
Catalin Bombea
Try:
=IF(H8-i8-f8<0,0,H8-i8-f8)
WASEEM.NM
=IF(E3=”above”,F3=D3,””))
I am using this formula to evaluate true or false as the answer. But not sure by what logic it is showing wrong as false /true in some of the cells and some of the cells the output is correct.
Regards,
Waseem.Nm
Mynda Treacy
Hi Waseem,
In English your formula says:
If the contents of cell E3 is “above” then F3 equals D3 otherwise blank.
In other words, if E3 contains “above” then Excel tests to see if F3 equals D3, if it does then it returns TRUE, if it doesn’t then it returns FALSE. I suspect you want something else in this argument.
Mynda
Chris
VBA for following function:
If a1 > b1 and c1 > d1 or a1 < b1 and c1 < d1 then lock cells e1 and f1 else unlock cells e1 and f1
Catalin Bombea
You almost finished writing it:
If ([a1] > [b1] and [c1] > [d1]) or ([a1] < [b1] and [c1] < [d1]) then [e1:f1].Locked=True else [e1:f1].Locked=False End If
Mzamo
Hi, I need help, I want to delete G3 if W3 is blank, how do I do this using which function?
Mynda Treacy
Hi Mzamo,
Formulas can’t delete values from cells. Only VBA can do that. The best you can do is have the formula return a blank e.g.
=IF(W3=””,””,the result you want)
Mynda
candice
I have a workbook with Sheet 1 and Sheet 2. In Sheet 1 colume B, each cell (going down the colume) has a number in it from 1.0-9.23. Each number has a matching statement of meaning. The numbers and their matching meaning are listed in Sheet 2 in columes A and B (A has the number, B has the definitions).
In sheet 1 colume B, when every a number is entered from 1.0-9.23, I want it to pull the matching defintion in the cell from Sheet 2 colume B. Can I do that?
Catalin Bombea
Sure, you can try a simple INDEX-MATCH combination in cell C2 of Sheet1:
=INDEX(Sheet2!B:B,MATCH(B2,Sheet2!A:A,0))
kay
hello can anyone help me. my formula doesnt work
=IF(H4=380000,”10000″,IF(H4>380000,”20000″,IF(H4>570000,”30000″,IF(H4>850000,”40000″,IF(H4>1050000,”60000″,IF(H4>1300000,”80000″,IF(H4>1800000,”100000″,IF(H4>2000000,”120000″))))))))
Mynda Treacy
Hi Kay,
It looks like you’re testing numbers, but returning text because all of your ‘value_if_true’ arguments are surrounded in double quotes. If you want numbers returned then you don’t need double quotes. Double quotes are only for text.
That said, you should use this VLOOKUP on a sorted list technique for formulas like that.
Mynda
Ared
I am in need of a nested formula that would allow for the following in one cell:
If A2=”A”, and B2=”I”, then C2=”Low”, or, If A2=”A”, and B2=”II”, then C2=”Low”, or, If A2=”A”, and B2=”III”, then C2=”Low”, or, If A2=”A”, and B2=”IV”, then C2=”Moderate”, or, If A2=”A”, and B2=”V”, then C2=”High”
My issue is there are 25 data possibilities. How do I write that in a formula?
I II III IV V
A Low Low Low Moderate High
B Low Low Moderate High Unacceptable
C Low Moderate Moderate High Unacceptable
D Low Moderate High High Unacceptable
E Moderate Moderate High Unacceptable Unacceptable
Catalin Bombea
You can try an INDEX MATCH formula:
Assuming that you have that table f values in a sheet named Sheet2, with I,II,III,IV, V in cells B1:F1, and A,B,C,D,E in cells A2:A6, try:
=INDEX(Sheet2!A1:F6,MATCH(A2,Sheet2!A1:A6,0),MATCH(B2,Sheet2!A1:F1,0))
Theo
Please help. how can I get Cell A1 to auto update with values from the information in the cells below the dates, on a daily basis?
Eg. Cell A1 = -1,26% (currently ties up with 02-Oct)
01-Oct / 02-Oct / 03-Oct / 04-Oct
6,56% /-1,26% /-1,63% / 0,00%
Cell A1 should now have -1.63% (should tie up with 03-Oct) and update daily thereafter with the value for that day.
Catalin Bombea
in A1 you should have:
=INDEX(A3:AA3,MATCH(TODAY(),A2:AA2,0))
In row 2 you should have dates, not dates in text format.
Jax
Hi
i need to auto fill data on column B when i write column A
like IF A:A =”done” then B:B =”done”
so if i write done on column A i need it to update automatically on Column B
please Help out
Mynda Treacy
Hi,
In B1 you have =A1 and just copy that down Col B
Phil
Siddu Goudar
80% -90%
91%-99%
100%-110%
111%-120%
>120%
how i can solve it
Catalin Bombea
=INDEX({1,2,3,4,5,6},MATCH(A1,{0, 0.8, 0.9, 1, 1.1, 1.2 },1))
Kalyani Jayram Kharde
There are 7 subjects out of 25 but having a rule if 1 subject having less than(<=7) then show result F1, for two subject having less than 7 then F2 otherwise Pass if greater than 7.
I can did it with countif function. It give me a count but i want count like 1 for F1, 2 For F2…..7 for Fail.
Please Help me…
Its Urgent
Mynda Treacy
Hi Kalyani,
Please post your question on our Excel forum with a sample file because our answer will depend on your data and file set up.
Mynda
Tara
NEED URGENT HELP
I need a column to spit out “Yes” when the term “Optum” or “United” is anywhere in the cell in another column.
So if any of these two terms are there, it should say ‘Yes’ in a new column.
Catalin Bombea
Try:
=IF(OR(ISNUMBER(SEARCH(“optum”,A1)),ISNUMBER(SEARCH(“united”,A1))),”Yes”,”No”)
Md. Hasnain
Basic Salary 600000 taka and House rent 60% of basic but I do not give him more than 20000 taka in House rent in this case what is the formula I should follow when I make a salary sheet.
Philip Treacy
Sorry, I don’t understand what it is you ar trying to do. Can you please try explaining it again in a different, clearer way.
Phil
JD
Hi. Can you please help me. I already have the IF formula where i want to generate a specific value from a column if between date range.
Example:
Column A1 Start Date, Column B1 End Date, Column C1 Headcount
Data in 2nd row: Start date Jan. 1, 2014 up to Jan.31, 2015, Headcount 6
FOrmula in Column D1
=IF(AND(D1>A2, D1<B2), C2, 0)
The formula is working fine but the problem is when there is blank or no date in End date column, there is no data generated. I would like to make use of one formula only that will work whether the End date has data or not.
Your help is very much appreciated. Thanks
Reply
Catalin Bombea
Hi,
It’s not clear what means for you “no data is generated” when there is no End Date. Your formula returns a zero in this case. What should be the expected result if End Date is missing?
Oggi
Hello,
has anyone have solution for this task.
I have kilometer range and the prices in onother column.
0-15 = 4,67
16-30 = 5,86
31-45 = 6,61
and so on.
I would like to input price for any range via some function.
For example if I input 19 km in another column gets te value 5,86.
Thank you guys for yor help.
Have a nice day
Mynda Treacy
Hi Oggi,
You can use a VLOOKUP on a sorted list as explained here.
Mynda
v
Total Days Income Expenses Remained Days remaining per day exp Days can spend
31 2000 200 1800 20 500
i want formula for how many days i can spend by per day 500 in remaining cash
Philip Treacy
Hi,
This isn’t clear to me.
If you have 200 income and 200 expenses then your remaining cash is 1800.
Where did you get 500 from?
Is 20 the number of days you want to split the 500 over? If so you just need 500/20?
You can get a whole number of days by using
=ROUNDDOWN(500/20,0)
Regards
Phil
jay
please help me regarding to my formula,
i just want to identify the days of pending, if cell C2 = 20/07/2019 05:21 then cell E2 my formula =IF(ISBLANK(C2),””,TODAY()-$C$2) “i want if c2 is blank so nothing will showed and if c2 have date and time will calculate the days of pending old date – date today,, example answer like this “2 days”
Catalin Bombea
Like this one?
=IF(ISBLANK(C2),””,Round(TODAY()-$C$2,1) & ” days”)
jay
i already try this one but not working the days not counting if how many days of pending. it showed only 0days, even i try to adjust the date.
jay
anyone can help for this one,,
C2(date and time) = 20/07/2019 05:21
D2(status, Pending or Resolved) = Resolved
E2(days of pending)=IF(ISBLANK(C2),””,IF(COUNTA(C2),$U$1-(C2),IF(D2=S2,E2=”Resolved”,””)))
i want to count the days of pending date today – C2 and if the c2 is blank e2 also blank, and if the d2 was pending still counting the days of pending but if the d2 is Resolved E2 will showed Resolved please help to correct my formula thank you and advance.
Catalin Bombea
Please use our forum, it’s much easier and faster to get help, you will be able to upload sample files so we can see your data structure.
Catalin Bombea
Hi Jay,
Please upload sample file here, on our forum, no idea what you’re actually doing there.
Val
“If they are full time, their bonus is 10% multiplied by the average of everyone’s salary, otherwise the bonus is 5% multiplied by the average of everyone’s salary”
I’m not sure about how to perform this on my excel worksheet.
Are they asking for the average of each person or all the employees’ salary?
Mynda Treacy
Hi Val,
I’m assuming there is only one salary listed per person, so you can’t find the average of a single salary therefore it must be the average of ‘everyone’s salary’, as stated in the question.
Mynda
eshwar
i want calculate the discount offer ,,, if the order value is 1000 the percentage of discount is 20% up to amount of 200 in excel
Catalin Bombea
Hi Eshwar,
What if the order value is less or higher than 1000? Can you provide some examples?
Karen Gustafson
Formula is =IF(($B$9+$B$13-$B$20)<0,0,round(($B$9+$B$13-$B$20),2)). Please define "<0,0,round" and ",2".
Catalin Bombea
Hi Karen,
Here is a translation:
“IF the sum of those 3 cells is less then zero, return a zero, otherwise round the sum of those 3 cells to 2 decimal places.”
Georgiana
Please help me with the function for this conditions, in one:
3 + 1 >240
2 + 1 >360
1 + 1 >480
Thanks!
Catalin Bombea
Hi Georgiana,
What 3+1, 2+1, 1+1 represents? They can never be over 240, or 360 or 480.
Hope
How do i calculate individual rating score as per rating scale of a staff using staff id as reference?
I need to find all 1 score ratings until 5 as the highest score rating
For example:
Rating Score Score
Staff Name: 1 2 3 4 5
Anna 5 3 7 8 9 – Total number of Ratings
Mynda Treacy
Hi Hope,
It’s difficult to picture the layout of your data to provide you with a solution. Please post your question on our Excel forum where you can upload a sample Excel file and we can help you further.
Mynda
Hari Lamichhane
I would like to use if function to pull data from certain cell, for example formula in cell B9, if value in H9 is greater 0, put data from B9 to B9, if value in H9 is greater than 0 than put data from C9 to C9, and so on and if value in H9 is greater than 0, then put value from H9 to H9.
If H9 is blank, go to the next cell and put value from next cell to B10, C10, D10. Below is sample data.
Date:B9 Chk# C9 Memo#D9 Payee#E9
01/05/2019 19757 May 2019 Rent City of Toronto
Account#F9 Amount#H9
22-01-00 – Accounts Payable $31,313.55
Data to be tested contained in column H. Column G contains other data.
Thanks in advance
Hari
Mynda Treacy
Hi Hari,
Thanks for your question. It’s a bit tricky to follow. Are you able to post it on our Excel Forum where you can upload a sample Excel file so we can see your question in context and help you further?
Thanks,
Mynda
Nageeb
I want Excel to do following in cell P3:
1- if N3 is Not Blank then Now()-N3, and if result of Now()-N3 equal to or grater than 3 years then it return “DUE” but if result less than 3 Years it return “VALID”
2- if N3 is Blank and L3 is Not Blank then Now()-L3, and if result of Now()-L3 equal to or grater than 3 years then it return “DUE” but if result less than 3 Years it return “VALID”
3- if N3 is Blank and L3 is Blank then Now()-J3 and if result of Now()-J3 equal to or grater than 3 years then it return “DUE” but if result less than 3 Years it return “VALID”
Looking for your support
Catalin Bombea
Hi Nageeb,
Try this:
=IF(MAX(N3,L3,J3)=0,””,IF(NOW()-MAX(N3,L3,J3)>=365.25*3,”DUE”,”VALID”))
Appiah Yaw Kusi
Hello, i want to get total aggregate of my students score in exams who offers 8 subjects. 4 of the subjects are compulsory to add up their grades after which i select two best out of the remaining four and add them up get my total aggregate. How do i go by that.
Example if i have 1, 3, 4, 1, 2, 5, 4 and 5 as the grade. The first four is compulsory to sum up their grades after which i select two grades out of the remaining four and sum all 6 grades up as my total aggregate. Pls what excel formular should i use.
Mynda Treacy
Hi Appiah,
Thanks for your question. Can you please post it on or Excel forum where you can upload a sample Excel file so we can better understand what you’re trying to do and help you with a solution.
Thanks,
Mynda
Amin Suchwani
income exceeds 1200000 but does not exceed Rs. 2,500,000 the rate of income tax is 5% of the amount exceeding Rs. 1,200,000 or Rs. 2000 which one is greater
Mynda Treacy
Hi Amin,
Where A1 contains the Income value.
Mynda
Hillary
I am having trouble with my if/and formula. I looking at ranges 0-.89=1, .90-1.09=2, 1.10+=3. My formula is =IF(AND(A2>=0,A2=0.9,A2=1.1,3)))
I am getting FALSE on .89, .90, 1.09 and 1.10 but everything else is assigned the correct 1,2 or 3. Example: .88 is coming back with 1 but not .89 is coming back as false and should be 1.
Any thoughts?
Thanks!
Hillary
=IF(AND(A2>=0,A2=0.9,A2=1.1,3)))
Not sure why my formula above didn’t copy correctly
Philip Treacy
Hi Hillary,
Copy from where?
Without knowing what your desired result is I can’t be sure how to fix the function, but this is valid syntax, although logically it will always be False as you have two tests for A2 to be equal to a value.
=IF(AND(A2>=0,A2=0.9,A2=1.1),3)
Regards
Phil
Philip Treacy
Hi Hillary,
Try this
=IF(AND(A2>0,A2<=0.89),1,IF(A2<=0.9,2,3))
Please note that if A2 is empty, 0 or negative, this will return a result of 2. This shouldn’t be the default answer but the AND condition makes it that way.
Either make sure A2 has a value inside your ranges, or include another IF to prevent erroneous output:
=IF(OR(A2<0,A2=0,A2=""),"",IF(AND(A2>0,A2<=0.89),1,IF(A2<=0.9,2,3)))
This will return an empty string “” if A2 is negative, 0 or empty.
Phil
Sanchez
Hi Sanchez again,
Thank you but the COUNT will just work for one department. I have multiple departments so would essentially need a formula to look at department and whether FTE or PT then grab number of positions and sum them.
Department FTE/PT # of Positions Available
HR FTE 1
HR FTE 1
HR FTE 1
HR PT 1
HR PT 1
IT FTE 2
IT PT 1
IT PT 1
Finance FTE 20
Finance FTE 3
Finance PT 2
Finance PT 1
Customer Support FTE 10
Customer Support PT 1
Customer SUpport PF 1
Catalin Bombea
Try:
=SUMPRODUCT((A$1:A$10=A1)*(B$1:B$10=B1)*(C$1:C$10))
Nora
IF CELL A1 =Jennifer then value will be 2000 please help me generate a formular for this
Catalin Bombea
Hi Nora,
=IF(A1=”Jennifer”,2000,0)
Sanchez
Department FTE/PT # of Positions Available
HR FTE 1
HR FTE 1
HR FTE 1
HR PT 1
HR PT 1
Jan
FTE
PT
Can you please help me with a formula to group and sum FTE and PT? Please use example above. The formula should be something like IF department is “FTE” = 3 and IF department is “PT”=2. Thank you!
Catalin Bombea
Hi Sanchez,
The easiest way is to add a pivot table: select the data range, from ribbon-Insert-Pivot Table-New Worksheet, then from the list of headers add the department to the row section, and the same department to the values section.
Or use: =COUNTIF(A1:A10,”FTE”)
Marc
Hello, i am running some assessments with various criteria to be met by candidates who are scored. Is there a way i can show their total score against a pre-populated “maximum possible”? So:
A B C D E Total
1. John 3 3 2 3 2 13/20 (or 13 out of 20)
2. Sue /20
3. Paul Out of 20
4. Chris
5. Abby
So the words “out of ‘total'” (or number equivalent) are already in col. F, but column F would also have a formula that calculates the candidates score and then puts the 2 together. I know we could simply write the words out of 20 in the column F header, but there are reasons (long, boring ones) why it has to be done this way
Thanks in advance
Marc
Catalin Bombea
Just use:
=SUM(B2:F2)&”/20″
A percentage will be relevant too, 100% means 20/20:
=SUM(B2:F2)/20 (format the cell as percentage)
Crispin
You guys are good
Mynda Treacy
🙂 thanks, Crispin! Glad we can help.
Robert
HI!
I am making a calculator which multiplies a value by a percentage. in some cases, we need to enter the word “Special” instead of the calculation.
Obviously I am getting the #Value! error.
I am not sure if this is correct:
=IF(OR(D4*I8), “SPECIAL”)
so I want it to enter the total of the multipliaction from D4*I18 in cell D13, or if the cell has the word “Special” in it, I want it to show Special in D13 instead.
Mynda Treacy
Hi Robert,
Assuming D13 is the cell that you want to test to see if it contains the word ‘Special’:
In English the formula reads:
IF D13 contains the word ‘special’, then return ‘special’, otherwise C4*I8.
Mynda
Sabrina
I need help with this formula, I have a multi worksheet workbook, in the summary worksheet I need the cell to just return a yes if there is text or a number in the other worksheet cell and a no if there if the cell is a blank on the other worksheet. I tired this. =IF(ISTEXT(vlookup(A7,’csa’!$A:$AC,14,False),”YES”, IF(ISNUMBER(vlookup(A7,’csa’!$A:$AC,14,FALSE)), “YES”, IF(ISBLANK(vlookup(A7,’csa’!$A:$AC,14,FALSE), “NO”, “”))) but it is too many arguments
Catalin Bombea
Hi Sabrina,
You are missing some paranthesis(after FALSE, there should be 2 closing paranthesis):
=IF(ISTEXT(vlookup(A7,'csa'!$A:$AC,14,False) ) ,"YES", IF(ISNUMBER(vlookup(A7,'csa'!$A:$AC,14,FALSE)), "YES", IF(ISBLANK(vlookup(A7,'csa'!$A:$AC,14,FALSE) ) , "NO", "")))
Sha
How do I write a formula IF cell A1= <4 then value will be a defined number…say 69?
Mynda Treacy
Hi Sha,
The formula above reads; if cell A1 is less than or equal to 4 then return 69, otherwise return 0.
Mynda
sha
Perfect.
What if there are more than one criteria? If cell A1 is less than 4 then return 69 or if cell A1 is between 5-9 then the return is 79?
Thank you
Mynda Treacy
Then you should use VLOOKUP on a sorted list like this.
Sha
Formula that will automatically calculate total price for any amount of units placed in C12.
Unit Quantity Price per unit
Below 4 36
5 to 10 34
11 to 30 31
31 to 5 26
51 above 20
# of Units C12
Total Price: ____
Mynda Treacy
Hi Sha,
You should use a VLOOKUP formula for this, as explained here: VLOOKUP approximate match.
Mynda
Abubakar Isiaq
Hi, what would be the formular to access the first 1000 from 20000, second 1000 from the 20000 and so on
Catalin Bombea
Hi,
Try this one:
OFFSET($K$1:$K$1000000, (ROW(A1)-1)*1000,0,1000)
In the first cell, this formula will return a range with first 1000 cells. If you copy this down, the second cell will have the second 1000 items, and so on. Use SUM, COUNT or your desired function with this range.
Shae
I am wanting to group employees into 3 categories (good, better,best) based off of their yearly averages but I was wanting that to auto populate. So if there yearly average false into a range (10-15%) they are good is there a formula for that. It’s seems as thought the if and works but would be very complicated seeing that it’s 3 categores
Philip Treacy
Hi Shae,
Yes this can be done. You haven’t specified the ranges for each of the 3 categories so it’s hard to give you a solution without all the information.
Can you please open a topic on our forum and supply a workbook with the data needed to provide you an answer.
Regards
Phil
Harry Staniforth
In a measured audit the auditor can give a 1,2,3,4 or 5 score. I want to transfer these to 0,25%,50%,75% and 100% respectively in the final sheet. Is there a formulae for this?
Mynda Treacy
Hi Harry,
If you have Office 365 you can use the SWITCH function. Otherwise, you’d have to write an IF function for each score.
Mynda
Catalin Bombea
Hi Harry,
You can try this:
=INDEX({0,0.25,0.5,0.75,1},Score)
Replace Score with a cell reference where you have that value.
Elizabeth Reading
I would like to highlight a row of cells if 3 or more cells in the row meet the criteria – is there a way to do this please? Alternatively, if a rule could be created to put a word in a cell if three or cells in a row meet the criteria then I could use auto formatting to highlight those rows. The difficulty I am having is trying to identify those with 3 or more (out of a maximum of 9) meet the criteria. Any help would be much appreciated.
Mynda Treacy
Hi Elizabeth,
You can use conditional formatting to highlight the row, as explained here. If you get stuck please post your question and sample Excel file on our forum where we can give you a specific answer.
Mynda
Rachel Vickers
Hiya. I have a list of cost types. If the cost type is Staff Costs, I need to be able to take the value of that staff cost (appears in another cell), find 15 percent of it, and detail what that is in another cell. I’ve got to that point, but it’s taking the value of costs from rows detailing other cost types, which means when I total it in ANOTHER column, it’s adding double.
I don’t know if that makes sense!
But this is what i have so far:
=IF(C4=”Staff costs (direct and external)”,M4 *0.15,M4)
But when I then sum this, as I said, the cells that dont have staff costs as the type are still pulling through, so any total is including this. How do I negate anything BUT staff costs (direct and external) in this formula.
ANY HELP much appreciated! Thank you
Philip Treacy
Hi Rachel,
Can you please open a post on the forum and supply you workbook, it’ll make it easier to understand your problem and offer a solution.
Regards
Phil
Elly
Hi Phil,
I’d need your advice on the following:
I currently have 2 columns of value – Column A and Column B. However, there are some “0” value and or “*KEY_ERR” in Column A.
I’m hoping for Column C to pick values in Column A as priority. However, if Column A has “0” or “*KEY_ERR” value, I’d then want Column C to pick the values in Column B. I was able to get the formula to work in Column C if Column A value is “0” but I’m not able to get the formula to work to reflect Column B’s value if Column A value is “*KEY_ERR” at the same time.
Could you advise? Thanks in advance!
Philip Treacy
Hi Elly,
In Col C you want this
=IF(OR(A1=0,A1=”KEY_ERR”),B1,A1)
Regards
Phil
Lisa Nelson
I tried to post a comment, but not sure if it went through.
I am trying to use an if then statement to get a date in column A to show up in COlumn B as just the month (but i am using it in a pivot table so it has to not be a date that is just formatted to show up as a month).
so i need if A2 contains or starts with January or 1, then return January in B2. eg. A2 is 1/2/19 and i want B2 to say Jan but as text.
dont know if you can help or not!
thanks
Philip Treacy
Hi Lisa,
In B2
=TEXT(A2,”mmm”)
Regards
Phil
Frances
I need the formula that would look at a cell and compare to another cell….
If cell i7 is equal to J7 then it is true if not it is false.
Thank you
Philip Treacy
Hi Frances,
There are multiple examples of this on the page, have you tried to work this out for yourself? The best way to learn is by doing.
=IF(I7=J7,TRUE,FALSE)
Phil
Racel
Hi there,
I need help with a simple formula in excel.
I’m creating a table for Debts.
So the actual sheet are consists of cells namely –Amount I owe– then several cells for –Payment– then a cell for the –Remaining Balance–.
The scenario is, if I owe 2000 then I wasn’t able to pay full but I only pay it for like 1000 then next time 500 then lastly 500.
So what could be the right formula for this matter.
Can you please help me if how the formula should be?
Hoping for your kind answers.
Thank you
Racel
Philip Treacy
Hi Racel,
Its always better to provide your data in a workbook.
This can easily be done but I need your workbook, it’s too complicated to explain clearly in writing.
Please open a topic on the forum and attach your workbook.
Regards
Phil
Nicole Villangca
Im doing a computation of tax. Here’s the scenario, for the Tax Exempt, the limit is 90,000.00.
What formula will I use if the total tax exempt not exceed 90,000 then there should be an excess amount, but if the total tax exempt exceeds 90,000 it should be zero.
Please correct this formula
=IF(D3>1,”Copy the number in D3″, “0”
Mynda Treacy
Hi Nicole,
Mynda
Jason
I am really new to excel and not too sure what formula to use.
Basically what I am trying to do is, if the value in B5 = 5 units, then for every 5 units that must equal to 15 points.
E.g. For every 5 units sold, that will then equal to 15 points
Not sure if this makes sense
Philip Treacy
Hi Jason,
I’m not entirely clear. Is what you are trying to say that for every 5 units sold, you get 15 points? So 10 units = 30 points etc?
If so you can use this =FLOOR(A1,5)/5*15
You can read up on FLOOR and CEILING
Regards
Phil
Steve Loveland
Hi all,
I am looking for a variation of what is proposed here. I am looking to autopopulate a list that goes something like this…
IF Cell A1 = x AND Cell B1 = y THEN C1 = z
where x is a service; y is a supplier and z is a shortcode.
While the formula is quite simple, I have 190 conditions! I am not sure if there is a better approach to this. I’m not sure if reading an array is the right approach.
Ideally, I will ‘teach’ the worksheet the codes for each service and supplier and run a macro or piece of VBA code over the list for the first two columns.
Is this the most useful approach? Should I be using VBA for this?
Philip Treacy
Hi Steve,
Impossible to say without seeing your data.
Can you please open a topic on the forum and supply your workbook.
Regards
Phil
JOE
CAN YOU USE AN IF STATMENT AND ALLOW FOR USER INPUT IN THE SAME CELL? I WANT TO USE AN IF STATMENT TO REFERENCE ANOTHER CELL; IF IT IS TRUE IT WILL ALLOW THE USER TO ENTER A NUMBER IN THE ACTIVE CELL AND IF FALSE IT WILL RETURN A STRING.
E.G. =IF(B5=”FIG #2″, USER INPUT, “N/A”) THIS WOULD BE TYPED INTO CELL C5 FOR EXAMPLE
Philip Treacy
Hi Joe,
No you can’t do this with IF, but you could use VBA.
Regards
Phil
Fabio MUscedere
I would like a formula for excel if cell G32 = 6.00 to 13.00 = ATHLETIC or 14.00 to 17.00 = FIT or 18.00 to 24.00 = AVERAGE or 25.00 Over = OBESE
Philip Treacy
Hi Fabio,
You’ve specified ranges of values where you have a specific result, but not specified what happens if the value is in between those ranges.
For example, what is the result if the value in G32 is 13.5? Or 17.5, 18.5 etc?
You can use this formula and adjust as needed
=IF(AND(G32>=6,G32<=13),"ATHLETIC",IF(AND(G32>=14,G32<=17),"FIT",IF(AND(G32>=18,G32<=24),"AVERAGE",IF(G32>=25,”OBESE”,””))))
Regards
Phil
Anwar Ali
I have a question for calculating incentive
If one sales man sales more than 75% of his target than 1% of sales amount and if sales was more than 100% than 2% or if sales was less than 75% than incentive will be “0”
Example
Sales Amount Target Achievement Incentive Amount
1130 1500 75% 1% ? (Need formula)
1510 1500 100% 2% ? (Need formula)
1000 1500 66% 0% ? (Need formula)
All need in one formula
Philip Treacy
Hi Anwar,
If Sales are in ColA and Target is in ColB the in ColC:
=IF(A1/B1>1,A1*0.02,IF(A1/B1>0.75,A1*0.01,0))
Regards
Phil
Michael Loewen
I have two sheets in an excel workbook. I need Sheet 2 to show the whole row 3 of Sheet 1 if the value in column 1 of Sheet 1 is 56
Philip Treacy
Hi Michael,
Do you really need to copy the entire row or can you limit it to certain cells? It would be better to limit it.
You also don’t say which cell in Col1 is 56, so I am working with A1
Try this on Sheet2 in A1 which will copy A3:Z3 from Sheet1. =IF(Sheet1!A1=56,Sheet1!A3:Z3,””) and you will need to enter this as an array formula by using CTRL+SHIFT+ENTER
https://www.myonlinetraininghub.com/excel-multi-cell-array-formulas
Regards
Phil
Ron Schmidt
Sheet 1 has rows of data on individuals. One cell value in the row determines if the entire row is copied from sheet 1 to sheet 2. Example: if K2 = 1 in sheet 1, all of row 2 (A through M) needs to transfer to sheet 2 automatically.
Philip Treacy
Hi Ron,
On Sheet2 in A1 you can enter =IF(Sheet1!K2=1,Sheet1!A2:M2,””) but you will need to enter this as an array formula by using CTRL+SHIFT+ENTER
https://www.myonlinetraininghub.com/excel-multi-cell-array-formulas
Regards
Phil
Abdul muneer
i need formula if A coloum(0 to 9)b colum(9 to 1)c coloum (0 to 9) ,d coloum a1(0 )then answer a1,a2,a3,a4,a5.same e coloum b2 then answer b3:b8 ,
Philip Treacy
Hi Abdul,
It’s not clear what you want or how your data is structured.
Please open a post on the forum and supply your data in a workbook.
Regards
Phil
Rena
Hello,
I am in need of a formula to find duplicates in 2 columns.
For e.g.
cell A1: 123 & cell B1: 1
cell A2: 345 & cell B2: 0
cell A3: 123 & cell B3: 1
cell A4: 123 & cell B4: 2
I need to identify that A1,B1 & A3,B3 are a duplicate.
Meanwhile A2,B2 & A4,B4 are not duplicate.
Catalin Bombea
Hi Rena,
Add a helper column with those 2 columns joined: =A1&B1
Then, in another column, just count how many times the current row values appear in the helper column:
=Countif(C1:C10,A1&B1)
Benjamin
Thank you so much for explaining all this. But i’m still a bit lost with this. I’m trying to add in a lunch break time in the formula below – if the total hours are over 6.
IF(OR(ISBLANK(C2),ISBLANK(D2)),DURATION(0),D2−C2)
Employee Date In Out Break Hours
Benji 12/18/18 12:00 PM 6:00 PM :30 6
Catalin Bombea
Hi Benjamin,
You are mixing time and decimal values. Keep in mind that the numeric value of 1 day is.. 1, that means 6 hours cannot be used as is, the unit of measure should be consistent. Therefore, 6 hours will be correctly represented by =6/24, because hours are just fractions of a day (6 hours=6/24=1/4=0.25= 1 quarter of a day).
On the other side, whenever you see a time format in a cell, 12:00 PM for example, keep in mind that it’s just a formatted value, the number in that cell cannot be over 1 unit, in this case 12:00 PM will be 0.5 decimal units. Just change the format of that cell to Number, and you will see the real value excel is using in calculations.
In your example, 6:00 PM – 12:00 PM will be 0.75-0.5=0.25. A lunch break of 30 minutes will be expressed as: 0.5/24, this is the value you need to add to your calculations.
Dejen
Hi sir, it is a question;
What is mean =IF(I4=N6,”-“,+H4/I4*31) in English.
Thanks,
Dejen
Mynda Treacy
Hi Dejen,
If the value in I4 equals the value in N6, then place a – in the cell, otherwise divide H4 by I4 x 31.
Mynda
Yugal
If value is between 27601-27649 then round down to 27600, if > 27650 then value should be 27700
the numbers may be any
Mynda Treacy
Hi Yugal,
Where your value is in cell A1.
Mynda
Desley Bee
How do I calculate the ‘age’ (B3) from dob (B2) AND if dob (B2) is Blank than to leave the ‘age’ column blank
Catalin Bombea
Hi Desley,
Here is an article about date calculations that should help.
If B2 is blank, just use a conditional formula in B3:
=IF(Len(B20=0,””,put here the age calculation formula)
Deb
I am trying to get my formula to return a certain dollar amount based on the number entered and where it falls related to the goal. There are 3 levels, therefor 3 tier payouts possible. The first part of my formula is working and returning the correct dollar amount but the part that has 100 in the formula is not working.
=IF(E5<B5,"$0",IF(E5=C5,75,IF(E5=D5,100)))))
Mynda Treacy
Hi Deb,
The formula looks ok except it has two too many closing parentheses.
I suspect that one of the first two conditions is being met and so the third condition E5=C5 isn’t getting an opportunity to evaluate. It’s difficult to say much more without seeing your file. You can post your question on our Excel forum where you can also upload your Excel file so we can help you further.
Mynda
DanielleK
I am trying to find a formula and am looking for some help. I have a spreadsheet of different grants that were put into a home. For some of the overall project dollars, the acquisition price of the home is counted and for others it is not. For example
House 1= grant $25000, owner contribution $25,000, acquisition $10,000, we are NOT counting acquisition.
House 2= grant $25,000, owner contribution $30,000, acquisition $40,000, we ARE counting acquisition.
Right now I have a Yes or No under column G for whether we are counting or not. How do I get a formula to automatically count the E column (acquisition cost), if G is Yes and not count it if G is No.
Philip Treacy
Hi Danielle,
When you say you want to ‘count’ the value in the E column, it’s not clear what you are doing with that value. But I can still write something that you can start with.
If your formula is in H1 then you could use something like
=IF(G1=”Yes”,E1+YourCalc,YourCalc)
Where I have written YourCalc is where you need to insert your calculation for whatever you are trying to calculate. Please note I have just added the value in E1 to YourCalc if G1 is “Yes”. You’ll need to incorporate E1 into your calculation in the way that is correct for you.
Regards
Phil
Zahir
I have data in two different columns like…
Description Amount
Tanveer Rs 460.00
Faisal Rs 50.00
Ahmad Rs 480.00
Tanveer Rs 550.00
Tanveer Rs 50.00
———————————
Grand Total Rs 1590
Now i want subtotal without adding these values where in the same row text is “Tanveer”.
Is there any formula which i use condition when in one column text is “Tanveer” then on this row in other column’s value will not add in total sum of all values without using filter or specify each cell number in formula…
I will be very thankful if someone help out on this situation.
Catalin Bombea
Hi Zahir,
Looks like you want to recreate what a pivot table does, with formulas. You should try a pivot table.
If you want formulas only, please upload a sample file with a sample manual result on our forum.
Grant Penney
Hi can you help with this please?
I have a formula in Cell A1 that shows a % to goal. I need a formula for Cell A2 that will show a number based on the % to goal showing in Cell A1.
>=102% = 5
>= 110%=10
>=125%=15
>=150%=30
thanks in advance,
Grant
Catalin Bombea
Hi Grant,
try this:
=INDEX({0,5,10,15,30},MATCH(A1,{0,1.02, 1.1, 1.25, 1.5},1))
Emilyn
I am struggling with a nested IF question. Trying to calculate which employees get how much percentage of a bonus at year end.
if E2 is greater than 9, they get 2%
if E2 is greater than 7,and less than or equal to 9, they get 1.5%
if E2 is between 5 to 7, they get 0.75%
is E2 is less than 5, they get 0.25%
Catalin Bombea
Hi Emilyn,
Try this one:
=INDEX({0.25, 0.75, 1.5, 2},MATCH(A1,{0,5,7,9},1))
Kristy
I am struggle on getting a formula to work correctly. Working on a rebate calculator.
If total>=3500=$100 rebate, if total >=7000=$250
>=15000=$750
>=25000=$1500
>=35000=$3000
What I write: =IF(A20>=3500,100,IF(A20>=7000,250,
Etc
I only get the lowest level calculated. What am I doing wrong?
Thank you.
Catalin Bombea
hi Kristy,
try this one:
=INDEX({0,100,250,750,1500,3000},MATCH(A20,{0,3500,7000,15000,25000,35000},1))
Kristy
Thank you. Trying now. Help much appreciated!!
Phil
Hi Catalin
I am not very good with Excel and formulas and I presume what I want to do can be done, I am just not sure how and what the formula might be.
In the first line I have headers There are 20 in total, but only 11 of these are colour coded. I was able to search how to add a “check box” and put these into the 10 spaces to show a different stage of progress, and when one of these boxes are checked there is the 11 cell that I would like to show as the “Status, so for each line in the status column and be the same colour as the checked tab. If I were to write the code it might look like this (remember I suck at code writing)
=D(COUNTSIF($E:$N)BOX MARKED IS CHECKED), COLOUR FILL WOULD = D
I am hoping to capture multiple lines, based on a line by line series
Catalin Bombea
Hi Phil,
Please upload a sample file with some data, and an example of the desired result. It’s not easy to visualize the data structure based on a description, a file will be more helpful than 1000 words.
Use our forum to upload (create a new topic.)
Phil
I need help to try and figure out a formula for a template I am working on. I am going to insert 10 “check Boxes” each with a colour code, when any of these boxes is checked, I want another cell in the line to change to the same colour as the checked box. It is meant for production and to signify each stage of that production so at a quick glance I can see what the “status” is and for argument sake let’s say column D is the status cell, and from E to N are the progress steps each with its own colour code. When one of these 10 columns is checked, I want the D cell to change to that colour, for every row item (and there could be hundreds), in any line there should be only two colours highlighted, The D and one of the E-N both with the same colour code. Does this make sense? It would be easier to show a chart I think
Catalin Bombea
hi Phil,
Each check box can be linked to a cell at your choice, right click the check box and select Format Control, in Control tab select a linked cell. In your formulas, you can refer to the linked cell to display colours as needed.
Dewald Serfontein
How do I do a calculation example. my answer is 49 then the IF statement says less than 50 and if my answer is 51 it says greater than 50 but now i also want a statement to say if it is between lets say 50 and 70 i want it to say between 50 and 70.
Catalin Bombea
Hi,
Try this formula:
=INDEX({“Less than 50″,”between 50 and 70″,”greater than 70”},MATCH(A1,{0,50,70},1))
In cell A1 (change as needed), you should have those values: 49, or 51 that must be evaluated by the formula.
Shazia Classen
need the cell to show “1” if B4 equals 7 and “0” if B4 is greater than or less than 7.
Catalin Bombea
try this:
=IF(B4=7,1,0)
Slim
Before entering the data under Product Unit Price, use a policy to accept price between $2 and
$20. Input text Title is “Input Range” with message=”Price between $2 and $20”, and Error Alert
having Title= “Wrong Input” and Error Message=”Invalid Value Entered”.
what formula do I use and how to do it ??
thank you
Mynda Treacy
Hi Slim,
You need Data Validation for this. If you’re still stuck after reading that tutorial, please post a question on our Excel Forum where you can also upload a sample Excel file.
Mynda
bETH
If Cell A is >= 9 AND Cell B is => 9 then Cell C = YES and both Cell A and B must have a number 9 or more. i am trying and it is allowing a blank cell B to still but a Yes in cell C
=IF(AND(a>=9,b>=9), “Yes”,” “)
Philip Treacy
Hi Beth,
You need to specify specific cells, not just ‘a’ or ‘b’
=IF(AND(A1>=9,B1>=9),”Yes”,””)
Regards
Phil
Wisk Derifond
Hi,
I am trying to calculate P=P1XP2 (Column K4) but I am finding errors.
P1=Improbable (Column G4), P2=Unlikely (Column J4)
I want P= Improbable
Thanks,
Philip Treacy
Sorry Wisk without seeing your data it isn’t clear how to calculate what you want. If you are trying to calculate P1 * P2 then isn’t it just P1 * P2?
Please open a post on the forum and attach your workbook.
Regards
Phil
Lea
Hi, may i asked you what is the meaning of this formula?
=IF(F3-5>0,(F3-5)*30+5*15,F3*15)
Philip Treacy
Hi Lea,
If you break it down into its parts it’s quite straightforward.
The syntax for IF is
=IF( TEST, Value if True, Value if False)
TEST = F3-5 > 0 in other words is F3-5 > 0 ?
Value if TEST is True = (F3-5) * 30 + 5 * 15
Value if TEST is False = F3 * 15
So if we have the number 10 in cell F3 and the formula evaluates to
=IF (10-5>0 , (10-5)*30+5*15, 10*15)
well 10-5 = 5 which is greater than 0 so this test result is TRUE which means the IF formula gives you the answer
(10-5)*30+5*15 = 225
Regards
Phil
SUMESH R KAMATH
Hi,
i need a formula for following calculation,
if X =< 21,000, then value should be 1.75*X , else the value should be 0(zero) [ ie, when X is greater than 21,000 then value should be 0(zero) ]
Mynda Treacy
Hi Sumesh,
Assuming X is in cell A1:
mYNDA
Masum
Dear,
I would like to request you, i have more than amount from one column,may be 1 to more than 1500,
i want to formula in excel under 50 of amount 10 %,up 100 of amount of 5 %, how to formula in excel,
please help me.
Lots of Thanks.
Masum.
Philip Treacy
Hi Masum,
It’s not really clear to me what you need. Can you please supply a workbook with real data so it’s easier to help you. You can create a forum post and attach your workbook to that.
Regards
Phil
Masum
Dear.
First for sorry.I can not explain to you. for Example 20,25,33,40,42,45,48,50 under fifty 10%
and 60,62,65……..100.105…..150 more of 5%
10=10%
20=10%
30=10%
35=10%
50=10%
51=5%
55=5%
58=5%
60=5%
70=5%
100=5%
150=5% etc.
condition of under 50 all sales of customer commission
for sales man 10% amount ,and up to 51 ,55,60,70,78,95,100,110 more,more
of 5% .
Philip Treacy
Hi Masum,
So what I think you are trying to do is calculate commission. 10% for values < 50 and 5% for values greater than fifty. Assuming values can't be less than 0, and the sales amount is in A1 =IF(A1<50,A1*0.1,A1*0.05) Regards Phil
Sanjay Gundlavkar
Can Anyone rectify my below formula.
Instead of Result, it shows me as #VALUE!
=IF(K3=0,”Nil”,TODAY()-C3)*AND(IF(K3>0,”Excess”,TODAY()-C3))*AND(IF(K3<0,TODAY()-C3,"Bal"))
Philip Treacy
Hi Sanjay,
With 0 in all the specified cells, you are multiplying a string (from the IF) with a Boolean result (from the AND) and you get #VALUE!
The use of IF inside AND isn’t correct either.
If you specify what you are trying to do and the expected result it’ll be easier to help you.
It looks like you are trying to say
IF K3 = 0 then the result is “Nil”
IF K3 > 0 then the result is “Excess”
IF K3 < 0 then the result is "Bal" I'm not sure where the TODAY() fits in. When asking questions like this please ALWAYS provide the source data otherwise we are guessing. You can start a post on the forum and attach your workbook to that. Regards Phil
Mohsin
Urgent Need Help in MS EXCEL FORMULA
Apply formula to fine the following result in above column “Result”:
If the student fail in Theory and pass in practical then result display “Fail in Theory”.
If the student fail in Practical and pass in Theory then result display “Fail in Practical”.
If the student fails in Both, Theory and practical then result display “Fail in B
Both”.
Mynda Treacy
Hi Moshin,
Please post your question on our Excel forum where you can upload a sample file. We need to know how Excel knows if the student fails or passes. How is your data laid out etc.
Mynda
Hasan
Hi,
I need If A1 (date) past 3 days then H1 cell color would be Blue, and If the A1(date) past 5 days then H1 color would be red. How can I do this ?
Can anyone give me shade light on this ?
Mynda Treacy
Hi Hasan,
It’s not clear how you’re calculating when a date is past 3 days/5 days. Is it from today’s date, or a date in another cell.
Either way, this post on how to apply conditional formatting using formulas should help.
Mynda
Hasan
Hi Mynda ,
Thanks very much for your effort and replied me so fast…..
Lokule
It was more helpful to me
Sujit Pereira
If column A contains month and column B contains names of clients. If the Name of a client has arrived before in same month earlier value in column C should be 0 and if it is first time value incolumn should be 1
Catalin Bombea
Hi Sujit,
Add a new column with this formula:
=A2&B2 (a simple concatenation of first 2 columns)Drag the formula down to your range.
In cell D2, add this formula:
=IF(COUNTIF(C$2:C2,A2&B2)=1,1,0)
Drag this formula down, it should return the expected results.
Catalin
hemant
how do i write formula in excel if cell A2 =Y then drop down list , if A2 =N then that cell should be non editable
Catalin Bombea
Hi,
A formula will never lock a cell, so you need to reformulate your question. Only with vba you will be able to set things your way.
Regards,
Catalin
Svea Mason
I am trying to copy a row of data from “PP Part 1” Tab (worksheet) to “SharePoint” Tab (worksheet) if AJ# <= 2. I want the formula to skip the copy if AJ# is not <= 2 and then evaluate the next row to see if it should copy without leaving blank lines on the "SharePoint" Tab (worksheet). # is equal to row number.
Basically "SharePoint" Tab (worksheet) should just be a list of all rows where AJ# <= 2.
Mynda Treacy
Hi Svea,
If you want to extract a set of values that match a criteria then I’d use a PivotTable with a filter for values AJ# <=2. If you need help setting it up, please post your question and sample Excel file on our forum where we can help you further.
Mynda
Jeremy
Hi there,
I am trying to have a cell automatically fill in a choice of two phrases depending on what the value is in another field.
So, here is what I am trying to achieve:
if P10 = Yes, then I want S10 to say “Required” as well as
if P10 = No or Unsure, then I want S10 to say “check requirements with provider”
Catalin Bombea
Try this formula:
=If(P10=”Yes”,”Required”,”check requirements with provider”)
For any value other than Yes, the formula will return “check requirements”
If you want those strict cases -No,Unsure, try this:
=If(P10=”Yes”,”Required”,If(OR(P10=”No”,P10=”Unsure”),”check requirements with provider”),”Other case”)
Rob
I have a spreadsheet that shows occurrence on a given date
Column A is the date, Column B shows “1” if there is an occurrence on that date EG
25/11/2018 1
26/11/2018
27/11/2018
28/11/2018 1
29/11/2018
30/11/2018 1
What I am trying to do is take the current date that the spreadsheet is open (that’s the easy part (today()) and look back over the total occurrences from the previous 90 days.
to put it simply what is the total number of occurrences in the last 90 days
Philip Treacy
Hi Rob,
Occurrence of what?
Are you trying to count how many times the workbook has been opened on a particular day? And then total that for the last 90 days?
Regards
Phil
Kamlesh Dhuria
I have to calculate who is L1 from three columns also if any duplicate values are there then the result should show FALSE
e.g.
Column A Column B Column C Result: FALSE
1 2 2
Column A Column B Column C Result: Column A
1 2 3
Catalin Bombea
Hi Kamlesh,
Question is: what L1 means for you? The smallest value?
Try this formula:
=IF(SUMPRODUCT(COUNTIF(A1:C1,A1:C1)*1)>3,FALSE,SUMPRODUCT((A1:C1=MIN(A1:C1))*COLUMN(A1:C1)))
It will return the column number of the smallest value.
mohan
find a calum this & if you found this count this as 1 & count this as 0.5 in excel
Philip Treacy
Sorry Mohan it’s not clear what you want. If you can open a forum post and include a workbook with data that will help us to help you.
Regards
Phil
Debojyoti Ganguly
Hi,
I Need a formula as below mentioned.
I have total material lifting date and after completion of work old material submission date. How can I get the ageing between them and also in that cases where I have not submitted the old material but lifted new material.
Catalin Bombea
Hi Debojyoti,
Can you please upload a sample file with your data and expected results? Hard to imagine your data structure. Use our forum to upload (create a new topic after sign-up).
Kat
Hi,
I need a formula that is a mix of IF, OR and AND. Can you help?
I need to stack a formula that says if column A = A or B and column C > year 2015 and column D has a value greater than 500, Column E must also contain the text “ABCD”, “EFG” OR “XYZ”. if this criteria is met “Include” if not then “exclude”. Is this level of criteria possible? I can’t get the criteria to meet it. Thanks
Catalin Bombea
Hi Kat,
try this in column F:
=IF(AND(OR(A2=”A”,A2=”B”),YEAR(C2)>2015,D2>500,OR(ISNUMBER(SEARCH(“ABCD”,E2)),ISNUMBER(SEARCH(“EFG”,E2)),ISNUMBER(SEARCH(“XYZ”,E2))),”include”,”exclude”)
Liz
I’m trying to get a formula to add 18 months or 36 month based on a word in a cell. For example,
If cell A2 has the word “Maintenance”, “Pressroom”, or “S/R” in it, I need the date in C2 to add 18 months to the date in B2, but if A2 has any other text, then C2 needs to add 36 months to the date in B2.
I have 3 departments that get reimbursed every 18 months, and all other departments get reimbursed every 36 months. How can I set up a formula to note when their time comes for another reimbursement?
Mynda Treacy
Hi Liz,
Try this:
Mynda
Shelly
Hi, I have a price in C2, that affects the rest of the sheet, if it goes up or down in price, what would be the formula? So that the other colums add or minus the £10 difference. Many thanks
Philip Treacy
Hi Shelley,
It’s not really clear what you want. I don’t know what formula you want in other cells. What $10 difference? Are you adding/subtracting 10 from a total somewhere? In which case wouldn’t you just use -10 or + 10?
Maybe if you open a forum post and supply some sample data in a workbook it will be clearer.
Regards
Phil
Shelly
Hi Phil
Sorry. I will try to explain better. The LME @ 4300 (in C2) can vary by £10; so I’m looking at when this changes happens, it will change the values C7:C20 C23:C30, and a few others across the book.
LME 4300
COPPER FRED CODE £
DRY BRIGHT WIRE (free from grease)
CLEAN ELECTRO (free from tinned ends)
CLEAN COPPER TUBE (free paint, solder)
HARD DRAWN WIRE (free from cd)
GREASY BRIGHT WIRE (max 5% greasy wire)
98% COPPER (HEAVY COPPER)
NO 2 COPPER (94%)
CLEAN COPPER TANKS (no scale/elements/brass 92% min)
BRAZIERY (88% min / no free brass)
LEAD WASHED RADS (78% min)
CLEAN PYRO CABLE(min 66%)
PVC PYRO CABLE (min 56%)
ELEMENTS (min 52%)
Philip Treacy
Hi Shelly,
I’m afraid it’s still not quite clear, that’s why I suggested you open a post on the forum and include your workbook with data, it’s much easier to understand when we can open a workbook.
One thing I don’t get is that you say the value in C2 changes various other cells, but how do you want it to change these cells? Where does IF fit into this situation? Haven’t you already written the formulae that determine the value of C7:C20, C23:C30 etc?
Regards
Phil
Jim Baker
Hello,
Need an hour formula to separate 40 hours from total hours or show total hours if >40.
example: Total hours in Cell A1 are 72. In Cell A2 I want to say if A1 is >40 than 40. If A1 is =to or 40,”40″)???
Philip Treacy
Hi Jim,
I’m not really clear on the logic of what you want.
show total hours if >40 is different to if A1 is >40 than 40
and I don’t know what this means
maybe try this in A2 and adjust it to suit
Phil
Kei
Hello ,
What is the formula if cell A is Negative I want to appear it to zero and if its positive i want it to multiply by 30%?
Thank You.
Philip Treacy
Hi Kei,
If you are testing a value in A1, then in A2 you can have
Regards
Phil
Damon Ak
I need a formula that allows me to assign a value to one box (i.e. D2), based on the value of another box (i.e. E2).
If E2 is between 0-10, D2 = 100
If E2 is between 11-20, D2= 75
If E3 is between 21-30, D2 = 50
If E3 is between 31-40, D2 = 25
If E3 is between 41-50, D3 = 0
Anyone can help me with that?
Mynda Treacy
Hi Damon,
You should use this VLOOKUP on a sorted list technique for this.
Mynda
Kent E Durbin
I want formula for IF D15 is more than 5 and less than 10 then “GOOD”
Philip Treacy
Hi Kent,
=IF(AND(D15>5,D15<10),"GOOD","BAD")
You haven't said what you want the result to be if D15 does not meet your criteria, so I've chosen to have BAD in the cell in such circumstances.
Regards
Phil
Leon Coetzee
How do you calculate the value of c1 using a reference in a1, in other words, excel must calculate the sum in c1 if reference in a1 is “TAM”
Mynda Treacy
Hi Leon,
=IF(A1=”TAM”,C1,0)
Mynda
K.RAJAGANAPATHY
sir, i want a formula for in excel to IFAND FORMULA OR VLOOKUP
H.R.A TO basic pay of 6% of subject to and minimum Rs-1400,and maximum Rs-2400
for example
basic pay 10,000 result 1400
basic pay 20,000 result 1400
basic pay 30,000 result 1800
basic pay 40,000 result 2400
basic pay 50000 result 2400
Mynda Treacy
Try this:
Mynda
Ayla
I am creating a form to request a guided tour. We offer our tours at different times depending on the day of the week. I have the Microsoft Date Picker on the form so that users may pick their date on a calendar and I would like another cell to show a certain drop down list if the date they chose is a Tuesday and another drop down list if the date they chose is a Wednesday, etc. How can I do this?
Philip Treacy
Hi Ayla,
If you can open a post on the forum and supply your code it’ll be easier for us to help you find a solution.
Regards
Phil
Sejohn
. PAYE is calculated as a flat rate of 8% on any amount in excess of $25,000. No PAYE is deducted if the salary is less than or equal to $25,000
i need a formula to help me work out if an amount is greater than 25,000 add 8% of the person salary if not no paye is deducted
so lets say my salary is in A1 and my qualifying income is in B1 then If(B1 is greater than A1 then 8% of A1 is Added to B1 formula
Catalin Bombea
Hi Sejohn,
Try this in C1:
=IF(A1>25000,B1+B1*0.08,B1)
Or:
=B1+IF(A1>25000,B1*0.08,0)
Zana
Hello all,
Im in this situation when i need to know when someone made a modification in the spreadsheet, meaning i need the date when the cell containment has been change.
I was trying with =IF(D2=”yes”, today()) … but is not working as in refreshing the date every time i open the spreadsheet.
And once i find the working formula, i would love to block it so no one could delete it or anything like that.
its for having control of the picking dates for specific parts.
Thank you so much!
Catalin Bombea
Buna Zana,
Only a custom code will be able to do that, as you noticed the formulas will update each time.
Can you upload on our forum a sample file with details of what you’re trying to achieve?
Catalin
Toni
I need help again. I’m stuck again it seems so easy. If and And formula. If sales are greater than 20,000 and have submitted a report (columns say yes or no) than they get 5000 dollars bonus if not 0.
Philip Treacy
Hi Toni,
Try understanding each function on its own before combining them.
So if Sales > 20000 then a bonus of 5000 is awarded. Otherwise 0 bonus. Assuming the value for Sales is in A1, the IF for this is
=IF(A1>20000,5000,0)
Looking at AND, it returns True or False. It returns True if the conditions you give it are both True, otherwise you get False. So, with either a yes or no in B1 you would write your AND like this
=AND(A1>20000, B1=”yes”)
This will give you True if both A1 is greater than 20000 AND B1 contains the text “yes”
Now all you have to do is insert that AND into the IF we wrote earlier
=IF(A1>20000,5000,0)
becomes
=IF(AND(A1>20000, B1=”yes”),5000,0)
Please note that checking for text like this is not case sensitive. “yes” is the same as “Yes” or “YES”.
Regards
Phil
Jay Soto
Looking for date formula calculation:
A1 cell is Widget 1, 2, 3, 4
A2 cell is date – I want to add 17 months if Widget 1, 2, and 3 are selected – if Widget 4 is selected I want to add 36 months.
Thank you for your time
Jay
Mynda Treacy
Hi Jay,
Try this:
Mynda
Toni
I need help with Or function. Employees receive 5000 bonus if their sales in 1St quarter or 2ND quarter are greater than 30,000. If not 0 bonus.
Philip Treacy
Hi Toni,
Without knowing how your data is set out I am assuming that Q1 Sales are in A1 and Q2 Sales are in B1
=IF(OR(A1>30000,B1>30000),5000,0)
I’d use another cell to store the threshold value of 30000 though, so it’s easier to adjust as needed. Assuming you store the value 30000 in D1 this would give you
=IF(OR(A1>D1,B1>D1),5000,0)
Regards
Phil
MIHA
HELLO
I NEED A FORMULA TO SHOW ME A TEXT IF ONE OF TWO STATMENTS ARE TRUE, SOMETHING LIKE THIS IF(OR(W3<0,W3=0),"TEXT"," ")
I TRIED TO USE IT LIKE THAT AND IT SHOWS ERROR
WHAT AM I DOING WRONG?
THANKS
Philip Treacy
Try this
=IF(W3<=0,"TEXT"," ") Regards Phil
Prabhakaran G
I want if formula in excel for Electricity bill calculate
That the concept is
0 to 100 units is free of cost
101 to 150 units is consumed, calculate amount Rupees 1.50/unit
and above 150 units consumed, calculate Rupees 2/unit
How is the if formula for Ms-Excel calculate , please send to my email
(0 to 100 Free, 100 to 150 *1.50, above 150 *2)
Mynda Treacy
Hi, I think this is what you’re after:
Mynda
AJ
Hello I need help making a formula for an Date Tracker. Essentially what I want to do is this:
Say:
A = Date of Training
B = Today’s Date
C= Color Red, Green, or Yellow.
Then I want a formula for multiple days that says, B is 365 days (or a year) passed A, then C will be red. Then if B is within 60 Days of 365 Days of B, then C will be yellow. Then if B is not passed 365 or within 60 days of 365 for A then C will be Green.
I wanted if the date is overdue a year to go red, within 60 days of that year to be yellow, and if they are not meeting those two then they are green
Catalin Bombea
Hi AJ,
Assuming that column C will contain the number of days between column B and column A, select the range from column C and insert a conditional formatting rule (Home tab, Styles section, Conditional Formatting – choose Icon Sets, any icon set you like.). In the next window, you will need to press the button Reverse Icon Order, they usually start with green, in your case must start with red.
The rules should be set as follows:
Red: when value is >= 365, type Number
Yellow: when < 365 and >= 60, type Number
Green: when <60
mangesh
Three products out of the 6 should achieve 100% of the unit wise target
– If achieved, 3% of the rupee value of any 3 out of 6 products
I want conditional formula of this condition
Philip Treacy
Hi Mangesh,
Please post this on the forum with a sample workbook. Its’s too hard to work out exactly what you want without seeing data and knowing what cells to reference.
Regards
Phil
JB
Hi, i have a sheet with a column (Q) thats adding with the basic sum formula. =Sum(K3+Q2) and it goes down in same column with same formula no more than 110 rows per sheet.
i made another column (R) and i want to use the =if(or formula. and im stuck.. i made another Column (Y) with starting value of 120 going down the column from Row2 down to Row 23. the are in increments of 120. so starts at 120 in Y2 end at 2640 Y23.
this is what i need help with. I would enter the formula in R3. If the value of Q3 is equal or greater than 120 but equal or less than 240, then Subtract Q3 – 120 , Or if its equal or greater than 241 but Equal or less than 360, then subtract Q3-240. or if its none add Q2+k3
and i would continue the formula until i reach 2640. Column Y signifies Hours and column Q as well
the purpose of this is to identify my operations by every 120 hours to reset and start a new day.
Mynda Treacy
Hi JB,
Try this:
If that isn’t what you’re after please post your question and a sample Excel file on our forum so we can better understand your question.
Mynda
Vir
in Column A, there are Buy and Sell.
Column B is Qty like 25,50,100,150
Column C is amount….like 500.00, 535.00, 600.00
now i wants to put the formula in “C” Column that if Buy then figure should be in Mines symbol (-500.00, -535.00, -600.00)
If Sell then it should be in + figure
how can i do?
plz suggest.
Thanks
Mynda Treacy
Hi Vir,
Try this in column D (note: you don’t say how column C values are arrived at, so all I can do is convert them to +/- in column D):
Mynda
Zackary Easley
What is the answer?!?!?! AHHHH
In the embedded Excel workbook below insert a formula (in the grey cells in column E) that calculates the bonus due for each salesperson. A $500 bonus is paid if a salesperson meets either target in cells C24 and C25, otherwise they earn $0 bonus.
Mynda Treacy
Hi Zackary,
The answer is in the workbook you can download for this tutorial.
Mynda
Loretta
Hi, I am trying to write a formula in conditional formation that evaluates a cell for >=and=90, F2<=100,"Excellent"))) but I have 6 evaluations for the same cell with different evaluation and a return different text, very good or good…..I understand I need to put the six different formulas together but I cannot seem to make it work.
Mynda Treacy
Hi Loretta,
A better function to use is VLOOKUP on a sorted list as described here.
Mynda
Dildar
Hi,
I need to put Note given below into this formula.
=IF(AT1350=400000,0,IF(AT1350<=800000,1000,IF(AT1350<=1200000,2000,IF(AT1350<=2400000,(AT1350-1200000)*5%,IF(AT13504800000,(AT1350-4800000)*15%+(300000)))))))
Note,{ Provided that where the taxable income exceeds Rs. 800,000/- the Minimum tax payable shall be Rs. 2,000/-
Mynda Treacy
Hi Dildar,
You can use the N function to add a comment/note to a formula e.g.:
=IF(AT1350=400000,0,IF(AT1350<=800000,1000,IF(AT1350<=1200000,2000,IF(AT1350<=2400000,(AT1350-1200000)*5%,IF(AT13504800000,(AT1350-4800000)*15%+(300000)))))))+N("Provided that where the taxable income exceeds Rs. 800,000/- the Minimum tax payable shall be Rs. 2,000/-")Mynda
Robson
I am trying to write a formula where if A2=65 and A2<75, the word "pass" appears on the cell, if A2<75, the cell is blank.
Philip Treacy
Hi Robson,
If A2 = 65 then it must be less than 75 too. And if A2 < 75 it can't be both 'pass' and blank? The logic there isn't quite right? Regards Phil
MONARK VADADORIYA
HELP ME :
if cell A3 is 0-50, cell B2 is 10-100 than cell B3 is 80
cell B5 is 20 as per A3, cell C5 is 60 as per cell B2 than cell cell D3 is 80 autocalculate by formula
than let me know which formula I use for cell D3
Catalin Bombea
Hi Monark,
Not sure I follow your logic, can you please upload a sample file on our forum (create a new topic after sign-in), with more examples of how the calculation should be? It will be much easier to understand your situation and provide a functional solution.Thanks for understanding.
Catalin
Dwarakesh A
If(A1>=B1:B10,1,0)
Please tell if this formula works, if not give me any alternate
Catalin Bombea
Hi Dwarakesh,
Depends on what you need: the value from A1 must be greater than all individual values from B1:B10 to return 1?
I yes, you can try this one:
=SUMPRODUCT(1*(SUM((A1>B1:B10)*1)=(ROWS(B1:B10))))
Derok
I am data in having 6 columns and many rows. I want to check if the date (column B) is less than today, then highlight the row. Rows will be getting updating.
I am using conditional formatting with formula =$B2<TODAY() to highlight the rows (A2:F2000). This is working but this is highlighting empty rows also. How to avoid highlighting the empty rows?
Catalin Bombea
Just add another condition, B2 should not be empty:
=AND($B2<>“”,$B2
Andy
This one doing my head in. I am sure its simple
For 1 – 50 widgets you buy you get 20 wingdings for free.
however for every additional widgets above 50 (to next 50) , you get an additional 5 wingdings and so on
so buy 49 widgets get 20 wingdings
buy between 51 and 100 widgets you get 25 windings
buy between 101 and 150 you get 30 wingdings, 151 – 200 widgets, 35 wingdings and so on.
Catalin Bombea
Hi Andy,
Yes, it’s simple, try this one:
=IF(A1<=0,0,20)+INT(A1/50)*5
Andrew McIntosh
Slight modification on the above;
Less than or equal to 600 widgets you get 0 wingdings
= to and above 651 you get 30 wingdings
than for every additional 50 widgets you get 2 wingdings
eg 651 widgets get 30 wingdings
704 widgets get 32 wingdings
810 widget get 36 windings
Thanks
Philip Treacy
Hi Andrew,
You don’t say what to do if the number is between 600 and 651?
Otherwise, you can use this
=IF(A1>=651,30+(INT((A1-650)/50)*2),0)
Regards
Phil
Moshood Oladimeji
I have an issue with the table below
Days of wk Time In Time Out Total Hrs Time In Time Out Total Hrs
Wed 12/28 3:30:00 PM 7:00:00 PM 7:00:00 PM 11:15:00 PM 7.75
Thu 12/29 12:00:00 AM 4:30:00 AM 8:00:00 AM 7:00:00 PM 15.50
Thu 12/29 7:00:00 PM 4:00:00 AM Nil Nil 9.00
Fri 12/30 8:30:00 AM 7:00:00 PM 7:00:00 PM 4:30:00 AM 20.00
Sat 12/31 8:40:00 AM 1:45:00 PM Nil Nil 5.08
Tue 1/3 8:40:00 AM 7:00:00 PM 7:00:00 PM 5:00:00 AM 20.33
Wed 1/4 8:55:00 AM 7:00:00 PM 7:00:00 PM 4:45:00 AM 19.83
Thu 1/5 10:35:00 AM 7:00:00 PM 7:00:00 AM 2:00:00 AM 15.42
Fri 1/6 9:10:00 AM 7:00:00 PM 7:00:00 PM 3:40:00 AM 18.50
Sat 1/7 9:00:00 AM 3:00:00 PM Nil Nil 6.00
1. How to calculate the total hours in the last column as it is shown ( take note how the figures are been written)
2. How do I remove the seconds aspect of the time i mean how do i change hh:mm:as to hh:mm
Catalin Bombea
Hi Moshood,
Can you please upload a sample file on our forum? It will be easier to work on your data, date calculations are sensitive.
Thank you
Catalin
marl
am trying to figure how to calculate this question on excel. please help if you can.
Pulitzer Photography pays young people to approach people in major department stores and offer them a free photograph if they come in to have their portrait taken.
They are paid $50, plus a commission of 2% of any sales of additional photos to the client up to $100, 5% of additional sales above $100 to $200, and 10% of additional sales above $200.
Mynda Treacy
Hi Marl,
Let’s assume your sales value is in cell A1, then you can use this formula:
Mynda
Mark
I have an issue.
I am setting up a chart based on units of run time for when it is due for a service and the cost. The example I have is:
0-2000 – cost $600 to maintenance
2001-4000 Cost $1750 maintenance and service
4001-6000 Cost $600 maintenance
I have the data as
C D E
1 0 2000 600
2 2001 4000 1750
3 4001 6000 600
Entry is:
A1 = 689 B1 =vlookup( A1,C1:E3,3,True) This displays the 600 total – all good
A2= 1378 B2 =vlookup( A2,C1:E3,3,True) This displays the 600 total – all good
A3= 2067 B3 =vlookup( A3,C1:E3,3,True) This displays the 1750 total – all good
A4= 2755 B4 =vlookup( A4,C1:E3,3,True) This displays the 1750 total – here is where I need help. How can I get it reconize that it had service at the 2067 mark and cost would go back to 600?
Can this be done or do I need to set it up differently?
Catalin Bombea
Hi Mark,
A4-2755 fits in the 4000-6000 range, with 600 cost, same as A3-2067, why are you saying that one should be 1750 and the other 600?
=INDEX({0,600,1750,600},MATCH(A1,{0,2001,4001,6001},1)) should give you the correct values.
Mark
Thank you!
I am saying that when the run time for the machine hits say 2001, there will be additional maintenance needed, but there isn’t additional maintenance needed until 6001 so the cost would go down to 600 for routine upkeep. I wanted a way that once it used the 1750 value it could go to the next cost which would be 600… if that makes sense.
Catalin Bombea
So between 2001 and 6001 there is no additional cost? What’s the 4001 limit for then?
I think you should provide a sample file with manual results, to clarify the problem, at this point it’s not very clear.
Create a new topic on our forum after sign-in and upload the sample file with a clear description and manual examples of how you need the results to be, there should be a solution.
Tonia
Hi,
Great article. I have a question. I have =A5-B5 cell to get C5 Value. But if I have no value in say B5, in C5 cell, I get 1. E.g A5=1, B5=0 , i get 1 in C5.
How do I write an IF statement to Say “none” in C5. If A5 has value and B5 has no value. say 1 – 0 = none.
Thanks
Catalin Bombea
Hi Tonia,
Try this in C5: =IF(B5=0,”none”,A5-B5)
Nakita
Hiya, I am currently doing a savings tracker based on countdown weeks to our wedding and how much money has been put into savings. Eg currently H32 has 21 weeks till our wedding written as 21 and H 33 has our goal left to save in those 21 weeks currently at 10,500. Each week we save $500. What I would like is that whenever I subtract a week so 21 to 20, i would like the savings box to move from $10,500 to $10,000 (subtract $500). Could you please help me with a code for this? There is not text in the grid, just the numbers 🙂
Catalin Bombea
Hi Nakita,
Can you please upload a sample file to our forum with a manual example of how the result should be? Hard to visualize the problem, a sample file will be a great help for us to understand hat you need.
Create a new topic after sign-in to upload the file.
Thank you
Javahar Babu
Hi Mynda, i need your help on below
155, 190, 250 and 300 value. in which these values should check 210 and should give the values as 155 – L1, where as it is less than 210, 190 – L2 it is next less vlaue when campared to 210, 250 – L3 it is bigger than 210 and finally 300 – L4. i have tried conditions in excel but i couldn’t through.
Catalin Bombea
Hi Javahar,
try: =INDEX({“L1″,”L2″,”L3″,”L4”},MATCH(A1,{155,190,250,300},1))
Catalin
vaibhav singh
i have 10+25 in one row and 13+28 in second row. I want that the total will come in third row but don’t want to use “=” before 10+25 and 13+28 as this data needs to show. Pls help
Mynda Treacy
Hi Vaibhav,
If you want to add values then they need to be entered as values/numbers for the formula in the third row. Without the = sign they are text and you cannot add text in your third row and return a value/number. In other words, it’s not possible. Perhaps you can put the text as desired in the column beside the proper values and then reference the correct values/numbers for your formula result in the third row.
Mynda
Kunjan Jain
My query is : say a cell contain pan no. AAACA1234A
whats the formula to get the result if fourth letter in the pan is “C” than generate result “company” and if letter is not “C” get result “non company”
Philip Treacy
Hi,
If your data is in A1 this is what you need
=IF(MID(A1,4,1)=”C”,”Company”,”Non Company”)
Regards
Phil
Kunjan Jain
Thanks a lot PHIL TREACY !!!!!!
Philip Treacy
You’re welcome.
Faisal Shahzad
Hi,
I need help to all of you
actually i am creating a incentive calculator for my Sales team
according to our incentive policy
if a sales person achive his monthly target then the total unit multiply with the rate,
here is 2 different situation apply
1st situation
if a person achive his monthly target >100%
for example
a person sale a product 110unit
100 unit is a monthly target
the 100 unit multiply with 100% slab and rest of the 10 unit multiply with >100% or 110% slab.
the slab are also mention here
<80% <89% <99% 100% 110%
56 62.3 69.3 70 87.5 105
kindly suggest me some better option for the calculating of these incentive
Philip Treacy
Hi Faisal,
Can you please open a topic on the forum and supply a workbook with sample data.
Thanks
Phil
Joanna
Hello everyone,
Desperate need of help as it is driving me insane. I am sure to myself that my formula is correct but not working for me at all so not so sure anymore.
I want cell F5 to have the value of 15 if Cell D5 and E5 have greater value than 1. The formula I am using is: =IF(AND(D5>=1,E5>=1),”15″,0)
Philip Treacy
Hi Joanna,
This formula works fine for me. You haven’t said what error you are getting and I don’t know what data you have in D5 and E5, is it text or numbers?
In your formula you have 15 in quotes “15” which means it is text, not a number. Nothing necessarily wrong with that if you want F5 to contain text though, but best not to store numbers as text.
Also, you have written ‘I want cell F5 to have the value of 15 if Cell D5 and E5 have greater value than 1’ but your formula is testing if the values in D5 and E5 are greater than or equal to 1.
If you can’t figure it out please post on our forum with your workbook so we can see the data.
Regards
Phil
Laura
Hi there,
I need some help with a simple formula I have to create. The description is the next one:
“If Stephanie has sales less than 600 in October, less than 750 in November, or less than 750 in December, the text No Bonus is shown, and if not, the text Bonus is shown.”
The table is this one:
” A B C D E
1. bonus entitlement___________________________________
2. sales person Oct.Sales ¦ Nov.Sales ¦ Dec.Sales¦ Bonus
3. Stephanie 637 829 726
4. Paul 819 939 826
5. Adam 512 721 637
6. Kaylee 728 843 945
7. Thomas 478 526 456
8.
I would really appreciate if you could help me cause I am really stock and I have no idea about excel formulas.
As far as I could get is this: =IF(A3<600
Which I dont even know if I am going well or not.
Kind regards
Laura.
Philip Treacy
Hi Laura,
For sales targets that have to be met every month :
This is assuming the values for sales start in cell B2.
Regards
Phil
NICOLE MCGUINNESS
i am trying to do a spreadsheet using sales – G10-14 have the profit in them, i need to then do a bonus column (K) – I need to do an IF formula selecting the highest profit and show they get a $2000 bonus. this is really getting me stuck!!
Catalin Bombea
Hi Nicole,
Can you please upload a sample file with some examples of how the results should be?
Use our forum to upload. (create a new topic after sign-up)
Regards,
Catalin
Nicole McGuinness
Have uploaded now on the forum
Catalin Bombea
Great, see you there.
hattan
I want to write this formula
If K: K=”in process” then calculate the number in E:E in E 300 otherwise don’t
Catalin Bombea
Hi Hattan,
What “calculate the number in E:E” means for you?
If it’s a sum, you can try this: =SUMIF(K:K,”in process”,E:E)
Cheers,
Catalin
satish
How to Add if value is
17/10
03/5
13/11
Total is
33/26
Please help to resolve it and share formula
Catalin Bombea
Hi Satish,
That’s not a value, it’s a text.
Split this into 2 columns, create the total for each column, then create the text string combining the 2 column totals.
Tracy
=if(and(B:B=”RR”,=A5:A300))
In other words, if Cells in B have RR in them, We want to count A5 thru A300. What am I doing wrong?
Mynda Treacy
Hi Tracy,
You don’t need IF, you need COUNTIF like this:
=COUNTIF(B:B,”RR”)
Mynda
Faisal
Sales Target 100% 90-99 89-80 70-79 <70
<80% 2.10% 1.9% 1.7% 1.3% 1.1%
<89% 2.40% 2.2% 1.9% 1.4% 1.2%
<99% 2.70% 2.4% 2.2% 1.6% 1.4%
100% 3.00% 2.7% 2.4% 1.8% 1.5%
106% 4.50% 4.1% 3.6% 2.7% 2.3%
i want to create a incentive sheet
if sales target is less then 80% and avg selling price is <70% then value cell*with 1.1
i am trying to calculate via this formula but the result shown false
=IF(AND(E8<80%,E9<70%),D12*N15,IF(AND(E8<90%,E9<70%),D12*N16))
plz correct it
Catalin Bombea
Hi Faisal,
The data table should look like this, in range A1:F7:
ST\SP 0% 70% 80% 90% 100%
0% 0% 0% 0% 0% 0%
80% 1.10% 1.30% 1.70% 1.90% 2.10%
89% 1.20% 1.40% 1.90% 2.20% 2.40%
99% 1.40% 1.60% 2.20% 2.40% 2.70%
100% 1.50% 1.80% 2.40% 2.70% 3.00%
106% 2.30% 2.70% 3.60% 4.10% 4.50%
Based on this setup, the formula is:
=INDEX($B$2:$F$7,MATCH(86%,$A$2:$A$7,1),MATCH(93%,$B$1:$F$1,1))
you can replace 86% used in the first match with a cell reference to your target sales, and 93% from the second match with your cell containing your average selling price.
Catalin
Faisal
AVG SELLING SLAB
Sales Target >=100% 90-99 89-80 70-79 <=69
106% 4.50% 4.1% 3.6% 2.7% 2.3%
actual table like this
Catalin Bombea
Hi Faisal,
I saw your current structure of your lookup table. If you want a formula to work, you will have to reformat your lookup table, like the one I sent, it’s the most simple structure that will need a simple formula. If you’re stuck with your structure, then the formula will be more complex, you will need helper columns to analyze an entry. Not recommended.
Ian
Hi, I’m hoping you can help me with a little problem that should be simple, i’m just missing it.
Basically what i want to return is:
Look up A1 on sheet 1 – Find A1 value on Sheet 2 in column A then return value for Column C in that row IF value from Column B from that row = xyz
ie, if i have a reference number on Sheet 1, i want to find that reference number on Sheet 2, and pull back a date value from Sheet 2 if the status = ‘in progress’.
Let me know if thats not clear?
Catalin Bombea
Hi Ian,
If you don’t want an array formula with multiple conditions, the easiest way is to set in column D a simple formula to merge columns A and B values, then your formula should look into column D:
=Index(Sheet2!C1:C100,Match(A1 & “in progress”,Sheet2!D1:D100,0))
If you want to use an array formula:
=Index(Sheet2!C1:C100,Match(A1 & “in progress”,Sheet2!A1:A100 & Sheet2!B1:B100,0)) (confirmed with CSE).
Ian
Thanks Catalin!! This worked out great (i used the Array formula).
Now, lets say i wanted to use an if statement, where if the value returned was an ‘#N/A’, then it would do a different function (A3-A5) for example.
So, IF (the formula you wrote) returns a number, leave the number, else perform (A3-A5)
Is that possible?
Catalin Bombea
It is possible, but you have to write the formula twice:
=IF(ISNUMBER(formula here), formula again here, A3-A5)
Array formula, of course. I would go for helper columns, if you have lots of formulas, it will be faster than array formulas.
Cheers,
Catalin
Ian
Hi Catalin,
I’m hoping i can call on your super expert help again for one more issue I am running into.
Everything we discussed prior is working as a dream. what i found is that on Sheet 2, there are multiple conditions that match (didn’t expect that initially) and it is only pulling back the first record in the sheet that it matches.
Here is what i’m using:
=INDEX(‘Sheet2’!$H$2:$H$100,MATCH(‘Sheet1′!B4 & “In Progress”,’Sheet2′!$A$2:$A$100 &’Sheet2’!$G$2:$G$100,0))
So this is basically a reference number on Sheet 1, that matches to a reference number on sheet 2, then when it MATCHES the reference number on sheet 2 it looks to match a second field “In Progress”, and if those two match returns a value from a 3rd cell.
What i’m running into is there are multiple rows that meet a condition where the reference number matches and it matches the second field “in Progress”. I need to have all the rows that meet this condition add the 3rd field together (the 3rd field is a numeric / time value)
Example:
match Reference number 001 in column A on sheet 1 to a reference number on sheet 2 in column A. then check that match to column B to see if column B for that same row has the text “in Progress”. then for each row that matches those two conditions, Add all the numbers from Column C and return the added number value.
hope i explained that so it makes sense? Any help you can provide would be super appreciated again!!
Catalin Bombea
Hi Ian,
It’s not an Index-Match any more, use this to sum all matches:
=SUMPRODUCT(Sheet2!$C$2:$C$100*(Sheet2!$A$2:$A$100=Sheet1!B4)*(Sheet2!$G$2:$G$100= “In Progress”))
Cheers,
Catalin
April
Hello,
I think what I am trying to do is relatively simple but I cannot get the formula just right or to apply to the whole spreadsheet. I need to take Column B of dates and have them automatically highlight red if they are more than 14 days from the dates in Column A.
For example I found that this formula works for each individual set of dates but I don’t want to have to type it in for each and every column.
=$B2>$A2+14 in the conditional formatting tools this works and but it’s not working for the whole table. I need A1 to coordinate with B1 and A2 to coordinate with B2 etc.
Anyone know how to do this? I know I am so close and it is driving me insane
Mynda Treacy
Hi April,
Your formula looks correct. You need to select the whole range of cells you want formatted before applying the Conditional Format. I explain how Conditional Formatting formulas work in detail here: https://www.myonlinetraininghub.com/excel-conditional-formatting-with-formulas
If you’re still stuck please post your question in our Excel Forum where you can upload a sample Excel file.
Mynda
Alex
Hi I have a problem that basically says that if B7=0 and B8>20000 then it’s a ‘yes’. If it’s false then I have to insert a nested if function (using AND) to determine if B7>0 and B8>200000 (when which case it’s a ‘no’ if false)
Basically it’s asking if the goal of 20000 was met if there were no fundraisers for the months of Jan, Feb, and April. But if there were fundraisers in the months of March and May then the goal of 200000 had to be met.
I’m just very lost and would love some help!
Catalin Bombea
Hi Alex,
Try:
=IF(AND(B7=0,B8>20000),”Yes”,IF(AND(B7>0,B8>200000),”>200000″,”No Condition met”))
Cheers,
Catalin
Wil
COLUMN A is a list of names, ROW 1 is a list of dates, intersecting cells have a value such as X: Can I write a formula to search by row 1 for the coordinating information from column A if the X value is present? For example, If A2 contains “Billy Bob”, A3 contains “Phil”, A4 contains “Charles” and B1 contains “April 3”, and B2, B3, B4 (the intersecting coordinates) has “X”, I want to be able to search by the B1 value with the result of all corresponding A values. So search: April 3, Result: Billy Bob, Phil, Charles. ANY HELP IS GREATLY APPRECIATED!
Mynda Treacy
Hi Wil,
I’d use a PivotTable. Put the names in the row labels and drag the date you want the list for into the values area. This will count the Xs against each name, then you can filter out any zero results.
If you get stuck please post your question in our Excel forum where you can upload a sample Excel file that we can put a solution into.
Mynda
Wil
THANK YOU SO MUCH! I will try that!
Rachel
Hi,
I am doing a leave tracker to record all the employees in a year.
There are 4 categories of staff with different leave entitled as well years of services.
Group M
2years is 18days, >5years is 22days
Group E, Group A
2years is 16days, >5years is 20days
Group C
2years is 14days, >5years is 18days
I need the excel will automatically define the correct leave entitled with group data
Is there any good formula to work up?
Seek for help, thank you.
Catalin Bombea
Hi Rachel,
Try this formula:
=INDEX(CHOOSE(INDEX({1,2,3},MATCH(C1,{0,2,5},1)),{0,0,0,0},{14,16,16,18},{18,18,20,22}),MATCH(D1,{“C”,”E”,”A”,”M”},0))
In cell C1, it should be a number representing the number of years, you can use a formula like this: =DATEDIF(A1,B1,”y”)
In D1, you should have the group name: C, E, A or M.
Cheers,
Catalin
jo
I need to have 5 emails sent from a worksheet with more than 50 attachments. The workbook would have 77 worksheet with this requirement.
And I want to share this with other people so that they can use it without any need to edit the code to personalize for them.
Mynda Treacy
What’s your question, Jo? Perhaps this tutorial will help: Create a PDF from Excel and email it with Outlook.
SPR
Hi,
Anyone can help me on this.
Current formula i am having now is below.
=ROUND(IF(AND(B6=”E”,C6=”BTT”),SUM(G6,I6)*5%,SUM(G6,I6)*3%),0)
Now i have a new condition. ie
A B C D E F G (E*F) H I(E*H) J
O 2 6000 3 18000 5 30000 480
O 1 3000 1 3000 2 6000 110
in “J” i need SUM of (1% OF “G” OR 50*”D” WHICHEVER IS HIGHER)+(1% OF “I” OR 50*D WHICHEVER IS HIGHER)
I need this criteria to be included in the above formula.
Catalin Bombea
Hi SPR,
Can you please uplload a sample file with your sample data and a manual result for that sample data? Use our forum to upload. (create a new topic after sign-up)
Catalin
Vinay Chavan
Merit List Decide the Eligibility of Student for the admission
Direct admission: if Total Marks is >600 & Sum of Maths Marks & science Mark is >=180
1st Merit List: If Total Marks is >=500 & Maths Marks >=55 & Science Mark >=55
2nd Merit List: If Total Marks is >=400 & Maths Marks >=55 & Science Mark >=55
3rd Merit List: If Total Marks is >=300 & Maths Marks >=50 & Science Mark >=45
Else not Eligible
Kindly solve the problems
Catalin Bombea
Hi Vinay,
Try this formula:
in column A: total marks, column B: Math marks, column C: Science marks.
ahsan
sum, count, average or any other formula not working on this below mention data. Furmulas worked when we rearrange cell with the help of F2 function key or double click on cell. Have any other option to rearange complete sheet or worksheet imediately?
Mynda Treacy
Hi Ashan,
I’m not sure what you mean, but maybe you have calculation set to manual? Check the Formula tab > Calculation options.
Mynda
maro
IF both A1+B1 7 but 9 but 20, return $200
I can not write it in Excel
Mynda Treacy
Hi Maro,
I’m not sure what you mean. Are you saying if A1+B1= 7, and are less than 9, then 20….???
Perhaps you can post your question and a sample Excel file on our forum we’ll be able to see what you’re trying to do.
Mynda
Uzma Razzaq
Some assistance needed please..
i need a formula for excel sheet. As i have record of fee of school students.
Condition: If more than one child of a parent studying in same school they will be granted fee compensation as
100% fee for first child
75% fee for Second child
50 % for all others & so on
if one child left the school, 100% will replaced by 75% &
75% will be replaced by 50%
what formula can i use for automatic replacement, pleas help me in this regard
Mynda Treacy
Hi Uzma,
It’s difficult to say without seeing the layout of your Excel file. e.g. do you have rows containing the names of each student and a corresponding column that has the count of family members for that student? If so it would be relatively easy, but if you don’t have that count then you need to calculate that first. Let’s assume you have the count of family members in column B beside each student listed in column A:
If you still have questions please post them in our Excel forum where you can also share the Excel sample file.
Mynda
Devon Chipman
Hello,
I have a formula that is giving me a headache. I am trying to do this:
Example:If cell B2=1 then cell C2 equals .043 or if cell B2=2 then cell C2 equals .087.
I am trying to type a number in B2 (1,2,3,4) and then have C2 put in the number that it is tied to or equal to.
Catalin Bombea
Hi Devon,
Try this formula in cell C2 please:
=IF(B2=1,0.043, IF(B2=2,0.087,”Other value in B2″))
Cheers,
Catalin
selvaraj
Landing Date & Time Delivery Taken Date & Time Del-Lan Result
3-4-18 22:00 3-6-18 12:01 38:01:00 Greater 24 Hours
3-5-18 10:30 3-6-18 12:01 25:31:00 Greater 24 Hours
3-7-18 4:14 3-7-18 6:35 2:21:00 LESS4
3-16-18 9:00 3-16-18 14:25 5:25:00 4 TO 8 HOURS
3-15-18 22:45 3-16-18 7:15 8:30:00 8 TO 12 HOURS
i did formula like this, but answer is not getting for greater 24 hours,
=IF(AI38<TIME(4,0,0),"LESS4",IF(AI38<TIME(8,0,0)," 4 TO 8 HOURS",IF(AI38<TIME(12,0,0)," 8 TO 12 HOURS",IF(AI38<TIME(24,0,0)," 12 TO 24 HOURS"))))
pl help me to share formula
Catalin Bombea
Hi selvaraj,
Date and time problems are very sensitive, there are many factors that can affect the calculation.
I think it’s best for you to upload a sample file with your sample data, this way we can analyze the formats you have and provide a functional solution.
Please use our forum to create a new topic and upload the file.
See you on forum.
Catalin
HABEEB RAHMAN
i have one doubt
i have one product 3600 PCS
last purchase date is 1st Jan 2018 -2300 pcs
last before 13th November 2017 -2300 pcs
whats is formula or program to calculate this ageing in excel
Catalin Bombea
Hi Habeeb,
Not sure what you want to achieve. Are you trying to calculate the difference between nov 2017 and jan 2018? This should be a simple deduction between those 2 cells.
Can you upload a ample file to our forum and clarify what you’re after?
Thanks
Catalin
Steve
Hello,
i am trying to do this formula
=IF(B3=1,AND(C3=5,15,PRODUCT(B3:C3)))
what i want to do is if cell B3 is equal to 1 and cell C3 is equal to 5, then i want it to return 15, Else i want Product of cell B3 and C3
Is this possible, if so What am i doing wrong?
Catalin Bombea
Try this:
=IF(AND(B3=1,C3=15),15,B3*C3)
Steve
That is Awesome, but i have 1 more condition to add in:
=IF(AND(B3=1,C3=5),15,OR(IF(B3=2,C3=5),15,B3*C3))
same thing, but i need to get the same result 15, or product if B3=2
am i doing too many steps?
if B3 is either 1 or 2 and C3 is 5, i need the result to be 15, or the product of the 2 cells
Catalin Bombea
Then it should be:
=IF(AND(OR(B3=1,B3=2),C3=5),15,B3*C3)
There are many ways to write this, here is another version:
=IF(OR(B3*C3=5,B3*C3=10),15,B3*C3)
In this case, it does not matter which cell has the 1 or 2 and which has the 5, so you should know better which one fits to your needs.
Steve
You are Awesome!!!!, have a great day
Catalin Bombea
You too 🙂
Cheers,
Catalin
Josh
Hi,
I’m trying to make a spreadsheet that can automatically assign a time slot to candidates that are successful in a test. For instance, Pass Test A then receive time slot. However, the difficulty comes in doing this for multiple candidates. For instance when the next person is entered as Pass Test A they would receive a timeslot 10 mins after the previous candidate. Is this something that can be automated?
Catalin Bombea
Hi Josh,
There is always a way, sometimes not exactly as your expectations, there may be design changes if something is not possible.
Can you upload on our forum a sample file so we can see your structure? (open a new topic after sign-in). A theoretical answer might not be what you need, we can try to find a personalized solution for you. Don’t forget to add as many detail as possible to clarify things, a fast response usually means that you did a good job explaining what you need 🙂
Regards,
Catalin
Pat
I need assistance. In the below chart if cells in column “A” equal 4656 AND the cell in column “G” equals 50, then I need to get the sum of only the cells in column F that correspond. I need it to look at the whole column of each.
A B C D E F G
1 8623 RHODES AARON 8623012287 3/1/2018 25.54 50
2 8623 HERRIN AARON 8623012288 3/1/2018 84.86 50
3 8623 JACKSON DYLAN 8623012286 3/1/2018 10.34 50
4 8623 RHODES DYLAN 8623012287 3/1/2018 47.88 60
5 8623 RHODES FRANK 8623012287 3/1/2018 31.92 80
6 4653 HEAD GLENDA 4653009783 3/2/2018 115.34 50
7 4653 HEAD GLENDA 4653009783 3/2/2018 154.81 60
8 4656 JOHNS GLENDA 4656017111 3/2/2018 388.59 50
9 4656 HART JADE 4656017109 3/2/2018 57.29 50
10 4656 JOHNS JERRY 4656017111 3/2/2018 477.12 80
11 8623 SIMMONS LATISHA 8623012293 3/2/2018 61.15 50
12 8623 MITCHELL LATONYA 8623012281 3/2/2018 28.8 50
13 8623 HARPER LATONYA 8623012292 3/2/2018 9.83 50
14 8623 SIMMONS PENELOPE 8623012293 3/2/2018 65.52 60
Catalin Bombea
Hi Pat,
You can try this formula:
=SUMPRODUCT((A1:A14=4656)*(G1:G14=50)*(F1:F14))
Johnson
I am putting together an inventory spreadsheet. I need 1 case to equal 24 units. (I’m inputting cases of beer). What formula do I use?
Mynda Treacy
Hi Johnson,
I suspect there are some details missing from your description, because at its most basic the formula is:
=1*24
If that isn’t what you were after please post your question and sample Excel file on our Excel Forum where we can better understand what you need.
Mynda
DJ
what is the formula to calculate to see if 3% is lesser than 30%
Catalin Bombea
Hi DJ,
Not sure what you mean, 3% will always be smaller than 30%. Can you give us more details?
Catalin
Kipp Play
=IF(C5>C3,”local”,IF(AND(C5>=5534,C5C3=local. Then I need two more answers, if C5>E3 = okay, but if C5<=E2 =More.
Catalin Bombea
Hi Kipp,
Try this:
=IF(C5>C3,”local”, IF(AND(C5>=5534,C5<>C3),”local”,IF(C5>E3,”okay”, IF(C5<=E2,"more","other")))) Nested ifs require a specific order of conditions, when the first true condition is met, the rest will not be evaluated.
Kipp Play
=IF(C5>C3,”local”,IF(AND(C5>=5534,C5C3, then place local in the filed. And if C5 is >=E3, then place Okay in the field, but if C5 is <= E2 then place Needs more in the filed.
I just can't seem to get the formula to work the way that I need it.
mohasin
in o26 value is 5000 and next cell o27 value is fix which is 10000 now i know that if cell o26 value is less than 10000 came cell o26 value but cell o26 value is more than 10000 than came only fix value 100000 plz tell formula
Catalin Bombea
Hi ,
Please use our forum to upload a sample file, your request is not clear, hopefully a sample file will clarify the request. Create a new topic after you sign-in to upload the file.
Regards,
Catalin
Subash
Hi Mynda,
Thanks for all the pains you and your team take to respond to queries of people visiting your site.
Regarding IF AND function, I am having a problem, kindly assist.
I have Nikhil in A1
in D1 I have put this formula
=IF(AND(A1=”Nikhil”,B1=0),”good”,IF(AND(A1=”Nikhil”,C1=4),” very good”))
even though I do not enter 0 in B1 I am still getting the result “good” in D1 whilst as I should get “very good” as I have put 4 in C1
I know excel treats 0 as some value, which I cannot remember.
Kindly assist as I have a list of 2600 rows to work with. -:)
Best regards,
Subash
Catalin Bombea
Hi Subash,
If B1 is empty, excel will consider it 0, so you need another check:
=IF(AND(A1=”Nikhil”,B1=0, Len(B1)>0),”good”,IF(AND(A1=”Nikhil”,C1=4),” very good”))
Or, put first the other condition, there is a specific behavior with nested IF statements: if a condition is met, the rest of IF statements will not be evaluated.
=IF(AND(A1=”Nikhil”,C1=4),” very good”),IF(AND(A1=”Nikhil”,B1=0),”good”,”other case”))
Subash
Thanks Mynda,
You guys are the greatest.
When I added the LEN function it worked. But it doesn’t work when I shifted the conditions as you suggested.
Thanks and best regards,
Subash
Catalin Bombea
You’re welcome, glad to hear you managed to make it work.
Catalin
Judy
I need help in getting a formula to help with the following. In my spreadsheet I have Hours earned for going to monthly training in cells D2 through O2. I also have the number 1 for each member if they attended the monthly meeting. I want to add the numbers in Cells D2 thought O2 ONLY if there is the number 1 in cell D5 through O5. An so on for the 75 members in the group. I want to put the answer in cells Q for each member.
I tried =SUMIF(D2,O2,D5,O5,1)
and I tried SUMIF(D2,O2,D5,O5,”1″)
Catalin Bombea
Hi Judy,
Can you please upload a sample file on our forum? (create a new topic)
It will be easier to provide a functional formula for your specific data structure.Thanks
Catalin
Carel van der Merwe
Good day,
I need your assistance please. I have two columns J & K which my information are in. “07/06/2017 05:06 PM” & “11/01/2017 02:08 PM” you can see the information in each cell contains the date as well as the time.. What i want to achieve is a true or false statement based on the time in each cell to calculate whether the difference between the two times are less than four hours of more than four hours difference. Meaning if less than 4 hours difference = true, more than 4 hours difference = false.
I hope and trust that my explanation makes sense. THANK YOU IN ADVANCE!
Catalin Bombea
Hi Carel,
If you want to ignore the dates and work only with times, you can try this:
=ABS(A1-INT(A1)-(B1-INT(B1)))>4/24
A time value will always be less than 1, as a day is an integer, one hour will be 1/24.
A1-INT(A1) will extract only the time fraction, ignoring the days, so it’s just a matter of deducting the time fractions and compare them with 4/24 (4 hours, converted to decimal system).
Catalin
Carel van der Merwe
Hi Catalin,
Thank you very much for you very much for your prompt response. I would like to take the dates into consideration as well seeing the i need to calculate the 4 hour difference =true/false? Is that possible?
So i basically need the calculation to be done on a calendar basis including time running on a 24 hour time format (am & pm) (“,)
Hiya did you get my response?
Catalin Bombea
Hi Carel,
Then simply deduct those 2 cells with dates:
=B1-A1>4/24
Carel van der Merwe
You’re a star! Thank you!
Catalin Bombea
You’re welcome 🙂
Sachin Vahile
IF THE GIVEN VALUE IN A CELL IS MORE THAN 3500 THEN DEDUCT 12 OR DEDUCT 6 FOR ANY VALUE
Catalin Bombea
Hi Sachin,
Try this:
=A1-IF(A1>3500,12,6)
Khan Fakhar
Dear i need help with the following formula , as i am making an estimation sheet for a paint company. for example a paint bucket is sufficient for 80 meter, if we take a job of 50 meter we have to use the same bucket and there will be wastage of 30 meter paint so we have to charge it to client. what i want is
if the value in the cell =if(i3 is greater then 0.1 but less then or equal to 1 then the value in cell E3 =1 ,but the i3 is greater then then 1 then its actual value to shown in E3 )
hope you can help me with this as i have be trying to figure it out since 3 days but i couldn’t make it.
regards
Catalin Bombea
Hi Khan,
Try this formula:
=IF(AND(I3>0.1,I3<=1),1,IF(I3>1,I3,I3))
Sahed
how to use in excel formula I’ll get 2,000 taka for every 3,000 taka
Sahed
i ll using this formula 12 column how can i use ?
Catalin Bombea
Depends on what you need to do. What do you have in those 12 columns? AND conditions, or IF conditions?
Please prepare a sample file with a manual result and detailed explanations, we can help only if the problem is clear. Use our forum to upload a file, create a new topic after signing-up.
Catalin
Catalin Bombea
Hi Sahed,
Try:
=ROUNDDOWN(A1/3000,0)*2000
Parveen Dhariwal
I want to put formula in excel like we have some students out of which some only understand english but not able to write and speak.
some understand and write but not able to speak.
some are able to read, write and speak all three.
then we have to categorized if someone is able to speak write and understand then he comes under excellent category.
If he understand and write comes under good category.
if he understand only then comes under average category.
if he is not able to speak write or understand then comes under poor category.
Kindly help in categorizing data as per requirement.
Catalin Bombea
Do you have 3 categories or 4? You mentioned Read, Write, Speak and Understand.
Each student should have 3 columns (or 4, if you have all those 4 attributes mentioned above), with values of 1 and 0. If a simple sum from those 3 columns returns the result 3, it will be “Excellent”, 2 will be “Good”, 1=”Average”, 0=”Poor”.
You can try a simple formula to return those categories:
=Index({“Poor”,”Average”,”Good”,”Excellent”},Match(Sum(B2:D2),{0,1,2,3},0))
This approach will return Good if the student has 1 ability, no matter which one, can be Read, or Write or Speak.
For more restrictive classification, you can use nested IF’s:
=If(And(B2=1,C2=0,D2=0),”Average”,If(And(B2=1,C2=1,D2=0),”Good”,”Other Case”)). You can add more cases until you cover all situations.
I assumed that in column A is the student name, column B is Read, column C is Write, Column D is Speak.
Sure
Hi i want a Coding that Cell J3 should be displayed as True if I3 < I4 to I9 I tried with this coding =If(I3<(I4:I9),"TRUE","") No rsults comes help me to solve this…
Mynda Treacy
Hi Sure,
I’ll assume that I3 must be less than all of the values in the range I4:I9. If so, you can use this formula:
Jean
Hi, i having situation as below. can the formula in 1 cell with below 2 IF situation.
in fty date later than produce date, can cell show late how many days, no late than show ok
if in fty date later than produce date <5 days and the production line start with K, can the cell show late how many days – ok
Mynda Treacy
Hi Jean,
Sorry, it’s a bit difficult to follow your example. Can you please post your question on our Excel forum and upload an example Excel file so we can see your question in context.
Thanks,
Mynda
Melinda Koite
HI! I think I need to use the IF function but please tell me if I’m wrong.
I’m making a spreadsheet for storage units. And I have columns with the months. I want to be able to put an x under the column month and it equal the amount of the storage unit rent in another column.
Mynda Treacy
Hi Melinda,
Possibly, but it’s difficult to say without seeing an example Excel file. Are you able to post your question on our Excel Forum where you can upload an example file so we can help you with a specific solution?
Mynda
lamees
If the amount is > 750, give a 10% discount.
Mynda Treacy
Hi Lamees,
Try this, where A1 contains your ‘amount’:
In English it reads; IF the amount in A1 is greater than 750, then take 10% off A1, otherwise the amount in A1.
Mynda
G.Harbs
I need some help on a statement. I recently made several changes to the way we track our estimating and I need to now track every project we bid as a count. Every week, we could bid on over 30 projects ranging from $20,000.00 to $2,000,000.00. I need to figure out a simple formula to track our bids as a count. In other words, I need to know how many bids we are submitting that are $0-100K, 100k-250k, 250k-500k, 500k-1M, $1M-$2M, and >$2M. Thank you for the help!
Mynda Treacy
Hi Gus,
I’d use a PivotTable for this. If you need pointers please post your question and some sample data in an Excel file on our forum and I’ll show you what I mean.
Mynda
Paul Smith
Excellent!! Very helpful.
Allen
All conditions are met in And If statement which should give True response, but it gives the False response.
‘=IF(AND($D66=”STPC”,$D66=”D-STPC”,I66>=10000),$I66/10000*3,5)
The STPC represents product type in column D, column I represents qty produced. If more than 10,000 are produced, since both logical conditions have been satisfied, should choose true, but it defaults everytime to answer 5.
Example: Row 71 = product type of STPC, and has a qty of 187000 in Column I which should give an answer of 18.70 * 3 = 56.10. But it defaults to 5.
My other And If statements work in other columns, but it is not working in column AN.
Mynda Treacy
Hi Allen,
There will be some anomaly in your data that’s causing the FALSE result. I’d have to see it to tell you why. Can you share the your question and file on our Excel Forum?
Mynda
samy
Thank you so much for the above explanation, however i have one scenario where i need help.
Excel Column A1. Excel Column B1
Price Highest price/ lowest price
1 NO
2
3
4
5 YES.
what i need is to populate yes & NO automatically wherever the price is high or low.
please help me with the formula on this.
Mynda Treacy
Hi Samy,
Assuming your values are in cells A2:A6, you can use this formula:
Mynda
Gk
Hello
If a person to get based on allocation,his billing type and he worked in full month so what function and what can I put the formula in excel…
Alloc:- allocation (based on )
B:- Billable
Nb:- non billable
Eg.
Name Alloc B/ NB start date enddate
ABC 100% B. 1/1/2017, 31/1/17
Plz help me
Awaiting ur reply
Thank you.
Mynda Treacy
Hi Gk,
Can you please post your question and a sample Excel file on our forum as it’s a bit difficult to understand from your description alone.
Thanks,
Mynda
RAJESH JOSHI
CELL VALUE IN PERCENTAGE A1 IS 50.00 SET CRITERIA IS BELOW MENTION
75-“3.00”
PLEASE URGENT REPLY …..THANKING YOU
RAJESH JOSHI
A1-50.00
A1 75 CELL VALUE IS “3.00”
Catalin Bombea
Hi Rajesh,
Assuming that A1 is a percentage, you should be able to use a very simple formula: =IF(A1=0.75,3,”This will be displayed for any values other than 0.75 in A1″)
Catalin
Jim Baker
I NEED TO WRITE A FORMULA THAT IS SIMPLE BUT FOR SOME REASON I CANT GET IT RIGHT. scenario. cell C1 has total hours worked. I want cell A1 to tell me that if C1 is greater than or equal to 40 than put 40 in A1. I also want A1 to tell me if C1 is less than 40 than put the number in C1 in cell A1
Mynda Treacy
Hi Jim,
Please post your question on our Excel Forum and include your example Excel workbook. Time calculations can be tricky so we need to see what format you’re entering hours etc. in order to help you.
Mynda
Sharlene Harding
Hi, Could I get help to fix this formula please
=IF(K4,”Y”)COUNTIF(B4:G4,”>0″)
Basically if a column has a “Y” count the amount of columns if the value is more than 0
Mynda Treacy
Hi Sharlene,
It’s a bit more complicated than an IF or even COUNTIF. You want to count data across multiple columns (B:G), as opposed to a single column, which is what COUNTIF is designed to do. You can use SUMPRODUCT like so:
Mynda
Paddy
Hi, I have a problem while using IF function.
See.. Am trying to use if function Column B & C with a formula that if column B contains CLOSED than C should be CURRENT date and the formula is ” =IF(B2=”CLOSED”,TODAY(),””) “.
It is working correctly, however, while opening the excel sheet in the next day, it is reflecting the current day and it is not reflecting the date which I actually enter CLOSED in column B on the particular date.
Can someone please help me to fix, what exactly am I doing wrong?
Thanks,
Paddy
Catalin Bombea
Hi Paddy,
Each time excel recalculates, TODAY function will return today’s date.
You have to use a simple macro that will react to column B values, when you type CLOSED, the code will put today’s date in column C.
Should be like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
For Each Cell In Target
If Cell = "CLOSED" Then Cell.Offset(0, 1) = Date
Next
Application.EnableEvents = True
End If
End Sub
The code should be placed in that worksheet vb module.
Ashok
i want to check whether a number is in range a1:e1. suppose if that number is in cell c1, i want to find the largest number in range c1:c10. wats the formula in excel
Mynda Treacy
Hi Ashok,
Please post your question and example Excel file on our Excel Forum, as I’m not sure what you mean by your explanation here.
Mynda
MD.JAHANGIR ALAM
Hi. How I Can Round In Bonus after Using IF(AND) Formula
Mynda Treacy
Try wrapping your formula in the ROUND function:
Will round the result to 2 decimal places, assuming IF returns a number. It’s difficult to be precise without knowing the details of your formula.
Mynda
ishmail dumbuya
Please i need a formula for this spreadsheet.
This is the summary of the formula i need.
discount of $37.9/MT for every pound below 49lbs to 47lbs. And discount of $75.8/MT below 47lbs to 46lbs and rejection for outturn below 46lbs.
Discount of $5/MT for every nut above 210 to 215, and above 215 to be rejected.
Discount of $20/MT for every 1% above 10% to 12% above 12% to be rejected. please i need this formula assistance. Thank you.
Mynda Treacy
Hi Ishmail,
Please post your question and a sample Excel file on our Excel forum so we can see your question in context of your data.
Thanks,
Mynda
jay_excel
Hi,
I need a formula to sum the values based on their respective dates and once the due date appears(monthly), it should start over again from that date until another monthly cycle.
Thanks
Mynda Treacy
Hi Jay,
It sounds like you need SUMIFS
Mynda
Jay
Hi,
Please advise a conditional formatting custom formula to highlight last Thursdays of every month in selected cells.
Catalin Bombea
Hi Jay,
Depends on what you have in those date cells.
In case you have dates, not text, you can try this conditional formatting formula, assuming that data starts from row 2 in column A:
=AND($A2>=EOMONTH($A2,0)-7,WEEKDAY($A2)=5)
Jay
Hi Catalin,
This has been helpful, undoubtedly. I tweaked the function a bit as follows, considering date column is AK and my weekdays start from Monday.
=AND($AK2>=EOMONTH($AK2,0)-7,WEEKDAY($AK2)=4)
Although this is perfect in almost all months, there is some error in few months.
The months ending on Wednesdays’ such as Jan 2018, Feb 2018, Oct 2018, July 2019, Sep 2020, March 2021, etc. shows the first Thursday of the next month i.e. 1st Feb 2018, 1st March 2018, 1 Nov 2018, 1st Aug 2019, 1st Oct 2020, 1st April 2021, etc. respectively, as highlighted.
L.E.:
The months, which are ending with Thursday’s, the formula is considering to highlight the last two Thursday’s of those months. Examples of such months are Aug 2017, November 2017, May 2018, January 2019, February 2019, etc.
Kindly help.
Kindly advise. Thanks..
Catalin Bombea
Hi,
Simply change the formula from:
=AND($AK2>=EOMONTH($AK2,0)-7,WEEKDAY($AK2)=4)
to:
=AND($AK2>=EOMONTH($AK2,0)-6,WEEKDAY($AK2)=4)
jay_excel
It worked.. Thank u so much.. Cheers 🙂
Jay
I need to create a formula where the text of the target cell, changes from “ACHIEVED” to “PENDING” on a basis of due date reoccurrence, every month. The sum reference cell values are obviously taken from different cells.
Here is what I got so far. My current status is “ACHIEVED”, but need this to start over again as “PENDING” on 15th of every month, irrespective of any other influencing factors.
=IF(X3>=S3,”ACHIEVED”,”PENDING”)
Please Advise
Catalin Bombea
Hi Jay,
We have to see some examples, try uploading a sample file on our forum. (create a new topic)
Catalin
LeNor
Hello. I need a formula that adds up currency in one column if the criteria in another column is met. I have tried a few and can’t get it to work with a range.
A3:A5 (has either a 1 or 2) and C3:C5 (has currency) I need a formula that says if range A3:A5 is 1 then add C3:C5 that coordinates with 1.
I hope that makes sense.
Catalin Bombea
Hi,
Can you prepare a sample file with an example of how it should be? At this moment, it’s not very clear. Use our forum to create a new topic and upload the file.
Catalin
Robert
What is the IF function for C2>E2=G2
Mynda Treacy
Hi Robert,
Mynda
naveen
Hai,
If 0 to 1000 value is 23
please advise some formula
Catalin Bombea
Hi Naveen,
use this one:
Emma
Hi Guys,
If I am using the formula: IF(AND(B2=”TRUE”,C2>1),”A”,”B”) where column B’s outputs (True or False) have been calculated with the OR formula (i.e. not hard-typed); I get the wrong output for my IF formula.
Any ideas how you get the right output when relying on information that has got formulas on it?
Thanks
Catalin Bombea
Hi Emma,
Try to use B2=TRUE, without wrapping TRUE between double quotes.
Catalin
Emma
Brilliant!
Thanks 🙂
Amit
hi admin,
Pls help to make a formula for, if in a cell (J6) is less then 1 then in cell(J7) populate 100%, If in cell (J6) is in between 1 to 5 then cell (J7) populate 50% and If in a cell(J6) is greater then 5 then populate 0%.
Catalin Bombea
Hi Amit,
Try this formula:
=IF(J6<1,100%,IF(J6<5,50%,0%))
You will have to decide if the limits should be included, use <= instead of <.
JJ
Hi, if I type in cell A1 a number between 0 and 20 and I want it to give in b2 =1 or 21 to 40 = 2, what formula do I use?
Catalin Bombea
Hi JJ,
try:
=IF(A1<=20,1,IF(A1<=40,2,3)) There can be many solutions, this is just one of them. Catalin
Dale H.
What is the three for?
Catalin Bombea
Hi Dale,
is for any other case, not covered by the first 2 evaluations.
Dale H.
In colum E are # 0-15.Im trying to create an equations that says if E is less than 1=40,if greater than 1 but less than 6 =80, if greater than 6 but less than 12 =120, but if greater than 12 =160
This is what created”IF(E3<1,40,IF(16,80,IF(612,120,160)))” anything less than 1 it gives me 40 which is correct but everything greater than 1 its giving me 80 which is semi true.
Catalin Bombea
Instead of nested IF’s, use a more flexible formula:
=INDEX({40,80,120,160},MATCH(E1,{0,1,6,12},1))Catalin
Namdeo
I am trying since long to create a formula in excel sheet using IF & AND function. My challenge is, I want Excel to update remarks automatically basis dates updates in different columns in my inventory sheet. However, I am not able to proceed.
Catalin Bombea
Hi Namdeo,
You will have to upload a sample file with details on our forum, from your description is not clear what you want.
Catalin
Symon Jere
How do I represent the following PAYE info. in an excel:
First 20000 is free (0%), the next 5000 its 15% and the excess is 30%.
E.g
Salary: 40, 000
First 20,000 free
Remaining 20,000
5,000 is 15% = 750
Remaining (excess) 15,000
15,000 is 30% = 4500
Catalin Bombea
Hi Symon,
Try this one:
Natasha
Hi,
I am trying to calculate depreciated values of assets based on number of years I have owned them.
the first problem is I am unable to do it all in one cell. moreover, when I split it up, almost all cells were returning values although i have limited my logic to one year. see below:
Price No of yrs depreciated value > 5 yrs 4-5 yrs 3-4 yrs 2-3 yrs 1-2 years
28,500 4.05 8,550.00 FALSE 8,550.00 14,250.00 19,950.00 25,650.00
the correct column for this example should be 4-5 year and this is the formula: =IF(5>H4>=4,G4*0.3,0)
I have created similar formulas for he rest of the columns and as you can see all are returning values although i have stated a value if false.
=IF(4>H4>=3,G4*0.5,0)
=IF(3>H4>=2,G4*0.7,0)
=IF(2>H4>=1,G4*0.9,0)
Catalin Bombea
Hi Natasha,
Use the AND function to combine multiple logical tests:
=IF(AND(4>H4,H4>=3),G4*0.5,0)
Ludi
Hope someone can help.
In column C, I have text.
In column D, I have account numbers. Their format is for example: 505.1605.130
Another account number will be 505.2450.100
Now the 16 in the first account number example indicates that it is an asset account.
Not if the account in column D is an asset account, I want the cell $H$8 displayed in column H and the row that the account is in.
BUT, in the same formula, I want to look up in column C (which is a text column) for the word Depn between multiple words, and also return $H$8 to the row in column H.
I’ve started with the following formula, but it still need a lot of work.
=IF(AND(LEFT($D$12,5)=1,LEFT($D$12,6)=6),$H$8,””)
Can it be done?
Catalin Bombea
Hi Ludi,
Use MID to find the account:=IF(AND(MID(D8,5,2),ISNUMBER(SEARCH(“depn”,D8)))=”16″,H8,””)
Catalin
rj
i want formula for (if A1999 than A1*6/100)
Catalin Bombea
Try:
=IF(A1999,A1*6/100,0)
You will have to specify the condition that should be met: A1999>0, or A1999=”Yes”
Kevin Frazier
I am trying to track sick time, I want it to be that they earn 1 hour for every week worked, max of 80. once they use some it should subtract that and keep tracking example
week 80 they are at 80
week 81 they are at 80 still
week 82 they use 8 hours so they are now at 72
week 83 they get 1 hour so they are at 73
can you do this?
Mynda Treacy
Hi Kevin,
Sure, but you’re best to upload a sample file to our Excel Forum so we can see your question in context of your data layout and give you a solution.
Mynda
Ashley Rader
Say I am doing Times in columns A and B and I want to do an IF statement that Returns a Green highlight If the time in column A is earlier than the time in Column B and Returns a Red highlight IF the time in column A is Later than the time in Column B, how do I do this?
Mynda Treacy
Hi Ashley,
You need to use Conditional Formatting for this. I cover that here: https://www.myonlinetraininghub.com/excel-conditional-formatting-with-formulas
If you get stuck please post your question and Excel file on our Excel forum where we can give you a specific answer.
Mynda
Shanu Singh
Hi,
I have a problem if somebody can help me it would be very appreciable. I had wrote many formula but did not get results.
Have data like:
Age Actual Value Exempted Value
50 30000
50 14500
50 25000
60 30000
60 19100
60 35000
We want to get results:
Age Actual Value Exempted Value
50 30000 25000
50 14500 14500
50 25000 25000
60 30000 30000
60 19100 19100
60 35000 30000
Note: There are upper cap for 60 year old 30000.
Thanks
Shanu
Philip Treacy
Hi,
You haven’t provided any logic/rules how to calculate that exempted value so I can’t help.
If you can provide this and a sample workbook, and post a question on the forum, someone might be able to help.
Regards
Phil
Shanu Singh
Sure Thanks
Ahsan
This is Ahsan. I have created a new logic which is mentioned below.
=IF(A20.75,A2*20%+A2))
by this logic any one can set any new target with below mentioned management criteria such as (if existing employee achieve 0 to 0.75 then his new target will be 0.75 but if existing achievement is more then 0.75 then 20% achievement will be add with existing achievement). Hope this logic will help with other.
Correction comments:
=IF(A20.75,A2*20%+A2))
Mynda Treacy
Hi Ashan,
I think you mean
=IF(A2<=0.75,0.75,A2*1.2) Mynda
Sara
Hello!
I am trying to create a slightly complex IF/THAN. If H2 = Late and G2 >= 30 then yes, otherwise no; If H2 = Early and G2 >= 15 then yes, otherwise no.
Column G is supplied via an ABS formula, and Column H are manual entries.
Thanks!
Mynda Treacy
Hi Sara,
Try this:
Mynda
Stace
Hi there, I am trying to do something similar but want to include multiple if’s in 1 cell to put different results if 2 different cells met certain criteria. How would I combine…I can not seem to get it to work
If H2= 1 and I2=”15oz” then =”EA” , If H2= 2 and I2=”27ft” then =”CT” ,If H2= 1 and I2=”13cm” then =”CA”
Appreciate any help
Thanks!
Catalin Bombea
Hi Stace,
Try:
Catalin
Praveen
If Column A is greater then Column B then Column C should given me “Yes” orelse “No”, if nothing contains in Column A and B then it should given me a blank cell
Mynda Treacy
Marty Chanbmil
Hi,
I am trying to have excel look up a cell in the same workbook – but per identity.
In this case cell 21 in each separate identity (represents a date/time entry via a macro.
This is reflected identically in the Summary Sheet in column A per identity =MickyMouse!A21.
When the new date is action via the macro in MickyMouse work sheet inside this same working book, the Summary sheet no longer reflects the current entry but rather changes the cell entry:
=MickyMouse!A22. HO do I automate this to be a true reflection, or a MACRO to check the column and make sure all end in !21.
Cheers Marty
Mynda Treacy
Hi Marty,
Please post your question and Excel file on our Excel forum so we can see your question in context.
Thanks,
Mynda
Steven
I am trying to create an expression and any help would be GREATLY appreciated! I am creating a worksheet with a little automation and would like to create an expression that outputs this example; If JANE DOE (name in column 1) will be arriving within the next 30 days (arrival date in column 2 listed as (date formatted) 21 January 2018) show “30 days or less” in column 3
Catalin Bombea
Try this:
=IF(Today()-B1<=30, "30 days or less", ">30 days")Catalin
Steven
Thanks for the reply. Its a good start but it doesn’t argue the date in column b, it just says “30 days or less” regardless. I tested it by changing the date in column b to 31 December 2018 and it returned with “30 days or less”
Catalin Bombea
Hi Steven,
For future dates, higher than today’s date, use B1-TODAY() in the logical test argument. If the date is smaller than today’s date, use TODAY()-B1. If you want this to work in both ways, use ABS(TODAY()-B1)
Cheers,
Catalin
Steven
Catalin,
Thanks so much that worked. If its not too much to ask, and im only asking because I cant seem to figure it out and you are already familiar with this problem.
I would like to output in the same argument “31-90 days”, “91-180 days”, “More than 181 days”
Thanks again for your time!
Catalin Bombea
Hi Steven,
Use an Index/Match formula with constants, like:
=INDEX({"Less than 30 days","31-90 days","91-180 days","More than 181 days"},MATCH(B1-TODAY(),{0,30,90,180},1))chell
Do you know how to create an excel formula for this: if b1 is x subtract 50% to a2, or if b1 is y subtract 100% to a2, the answer should appear on cell c2?
Mynda Treacy
Hi Chell,
I’m not sure I understand your question. This part is confusing: “if B1 is x subtract 50% to A2”. What do you mean by “x subtract”? Can you please provide an example using numbers so we can follow the logic and provide you with formula.
Thanks,
Mynda
Ram Mohan
If a worker worked for 15days, but he will be paid for 10days, due to poor performer. Similar for 50workers. What formula to be used in excel
Catalin Bombea
Hi Ram,
Please upload a sample file with your data and a better description of the problem on our forum (create a new topic).
Catalin
Alan
Trying to figure out a rolling time period of 75 days where the previous day and the data associated with it falls off.
Catalin Bombea
Hi Alan,
Try uploading to our forum (create a new topic) a file with sample data and an example of the desired result, it will be easier to understand your situation.
Catalin
Alan
Thank you Catalin, I will try that.
Alan
Trying to get a formula for dates older than 75 days. If B4>75 days then B4,C4,D4 and E4 will be blank. If B4 is< 75 days then there is no change to any cells. B4 has a date value in it. Any help is appreciated.
Catalin Bombea
Hi Alan,
It’s not possible for a cell to depend on itself and hold different values, depending on it’s own value. Only with vba this can be achieved, not with normal cell formulas.
Catalin
Alan
thank you
Madisen
I’ve been struggling to figure out how to type in this IF function for excel. I could really use some help! Thank you!!
In cell G2, create a formula using the IF function and structured references to create a calculated column that determines the value of Weeks Vacation based on the following criteria:
a. If the years employed are less than 3, the Weeks Vacation should be 2
b. If the years employed are 3 or more, the Weeks Vacation should be 3
I keep getting invalid credentials or the outcomes being all FALSE, TRUE and 0, all the cells being 0, or the first 3 cells being 0 and the last 3 coming out as NULL instead of the correct numbers… The correct answers when the formula is entered correctly is:
G2:3
G3:2
G4:3
G5:2
G6:3
G7:3
Thanks again!
Catalin Bombea
Hi Madisen,
If you have an employment date column, the formula should be simple:
=IF(YEAR(Today())-YEAR(EmploymentDate)<3,2,3) (where EmploymentDate refers to that starting date column)
If you already have the employment years calculated, replace YEAR(Today())-YEAR(EmploymentDate) with your cell reference where you have this value.
Catalin
Philippa Stanworth
I want to put formula in to say if column c says “6W” and column u is greater than 99, add the number of rows with 6W and >99 and divide them by 29 and multiply by 100.
Help!
Catalin Bombea
Hi Philippa,
Try this:
=SUMPRODUCT((C:C=”6W”)*(U:U>99))/29*100
Paul
I need to see if the value in A2 is 10% or higher than the value in B2…should I be using an IF function for this…I can’t figure it out
Mynda Treacy
Hi Paul,
You can do this with a simple logic formula like so:
If it’s bigger then it will return TRUE, otherwise it will return FALSE
You didn’t say what you wanted to return if A2 was 10% greater than B2. If you want to return something other than TRUE or FALSE (that will be returned by the formula above) then you’ll need IF.
Mynda
Paul
Thank you Mynda, I think this is a case where I was overthinking..
Kristi Gunerius
I have a partial answer to my question. I am trying to get an answer based on a date…
=IF(TODAY()>=EDATE($C$2,12*10,300,0) AND =IF(TODAY()>=EDATE($C$2,12*15),500,0)
I am trying to get this in one equation, but I can’t seem to get the two statements joined. They both are working on their own just fine. Basically I am asking that if today’s date is 10 or 15 years from the hire date, then the staff member receives either a 300 or 500 stipend.
Mynda Treacy
Hi Kristi,
It looks like your EDATE function isn’t correct. It only has two arguments; Start Date and Months.
Try this:
=IF(TODAY()>=EDATE($C$2,12*15),500,IF(TODAY()>=EDATE($C$2,12*10),300,0)
Mynda
Jahayra Gomez
can some one help me with a formula.
I want to create a formula tat states the following:
if a3 has a date of xx/xx/xx or grater then “40” but if a3 has a date of less than xx/xx/xx then it should be “0”
Mynda Treacy
Hi Jahayra,
Try:
Mynda
Virendra Sidhu
I am looking for a formula where if 268 (Column B) was scored out of 284 (Column A), which means it is 88% achieved (Column C), I need a formula that states if Column C is below 60% it is 0 marks, if below 80% then half of the defined mark which is 10 marks of defined 20 marks and above 80% to 100% then actual % of column C of 20 marks and if above 105% then double the marks of above 100% + 100% marks for e.g if 107% then 7% * 2 times for double which comes to 14%, so now 14% of 20 marks which is 2.8 marks + 20 marks = 22.80 marks
Catalin Bombea
Hi Virendra,
Try this formula:
=CHOOSE(INDEX({1,2,3,4,5},MATCH(C1,{0,0.6,0.8,1,1.05},1)), 0, 20*0.5, 20*C1, 20*C1, 20+20*2*(C1-1))
Your description is very confusing, and there is a range not covered: what should happen if the percentage is between 100 and 105% ? You described only 80-10 and above 105%. The formula for this case, which is value 4 argument of the CHOOSE function, must be changed according to this case needs.
Catalin
Prashant pawar
i want formula count late mark condition is if employee cross 100 min his deduction 0.5 late make per day if he late.
Catalin Bombea
Hi Prashant,
You will have to upload a sample file to our forum, and provide more information, at this moment it’s not very clear what you want to do, a sample file with your data structure will help clarify the request.
Thanks for understanding
Catalin
Edouard
I have this formula in the cell X2:
=If(AND(W2>=$X$10;W2<=$Y$10;C2″”;D2″”;E2″”;F2″”;G2″”;H2″”;I2″”;J2″”;K2″”;L2″”;M2″”;N2″”;O2″”;P2″”;Q2″”;R2″”;S2″”;T2″”;U2″”);”NAF”;”DEL”)
If these conditions are meet then return NAF otherwise DEL and it works.
But for me the formula is long, I tried to replace from C2 to U2 with C2:U2″” but is returning #Value!
Is there anyone who can simplifier this formula for me? Please I need your assistance.
Edouard
C2 to U2 not blank, the application removed the sign less than and great than after cell numbers.
Catalin Bombea
Hi Edouard,
Try this one:
=IF(AND(W2>=$X$10,W2<=$Y$10,SUMPRODUCT((C2:U2<>"")*1)=COLUMNS(C2:U2)),"NAF","DEL")You can replace COLUMNS(C2:U2) with 19, if the number of cells in that range is not going to change.
You will also have to change the comma separator to your semicolon separator in formula.
Catalin
Jafar Chhapra
You want to add the contents of cells A3 and B3, then add 15%, and display the total in cell C3. What do you type in cell C3 to make this happen?
Mynda Treacy
Hi Jafar,
Kind regards,
Mynda
Alex Wilkins
trying to add 10 years to a date if another cell = Y and 6 years if cell = N
would also like to change the color to recognize the N
a b c d e f
Y 03/01/2010 03/01/2020
N 03/01/2010 03/01/2016
Catalin Bombea
Hi Alex,
Try:
=DATE(YEAR(B1)+IF(A1=”Y”,10,If(A1=”N”,6,0)), MONTH(B1), DAY(B1))
Catalin
Mynda Treacy
Hi Alex,
I see Catalin has answered your formula question. You can use Conditional Formatting to automatically format the colour.
Mynda
saurabh Verma
Calculate the division for a student acc. to following conditions:-
1- If Total marks >=500, then division is 1st.
2- If Total marks is b/w 400 to 500, then 2nd.
3- If Total marks is b/w 300 to 400, then 3rd.
4- If Total marks is <300, then fail.
How can i do this plzz rpy me…..
Catalin Bombea
Hi,
Try this one:
=INDEX({0,3,2,1},MATCH(C15,{0,300,400,500},1))
Catalin
Elbeth
Hi,
Will you be so kind to assist me.
I am struggling to get a working formula for what i want to do.
The 2 cells referred to was pulled through with VLOOKUP or calculated with a nested IF formula,
=IF(E21=Saturday,F24*1.5,IF(E21=Sunday,F24*2))
E21: VLOOKUP Formula from another sheet which will indicate whether that day was a Monday, Tues,etc.
F24: IF nested formula to give a price
I want the total to automatically calculate the price x1.5 or x2 if it E21 is Saturday or Sunday, and normal pricing should remain if during weekdays.
However I have received a #NAME? error
I am still learning via all these helpful posts, will you please assist
Catalin Bombea
Hi Elbeth,
put the text between double quotes:
=IF(E21=”Saturday”,F24*1.5,IF(E21=”Sunday”,F24*2)), only defined names can be used without double quotes.
Elbeth
Thank you for your assistance,
I have tried this and yet again I receive the NAME error.
Is it possible that the Saturday and Sunday I refer to in the formula was converted from date directly with format cells dddd function?
Catalin Bombea
Are those double quotes copy pasted from the previous reply? If so, you will have to type them from your keyboard, those double quotes copied from a web page might be different than the type of double quotes expected by excel.
Also, you can create a new topic on our forum, to upload your file, it will be easier to understand what happens there.
Catalin
Jeanine
Hi,
I am busy writing a performance review document for my company. In column A will be the job description example “Sales”. Column B you rate the employee as either 1 or 2 = Poor, 3 = Average or 4 or 5 = Excellent.
I want the description (Poor, Average or Excellent) to come up automatically in column C when you type 1-5 in column B.
Please assist with the formula to use
Mynda Treacy
Hi Jeanine,
Try this:
Mynda
Jeanine
Hi Mynda,
that did work, thank you.
However, now i want to know, if the sell is blank, it give the if(B1<3, "Poor") rating. however, i want to change it to should the cell be blank, there shouldn't be any rating.
Please assist
Thanks
Jeanine
Mynda Treacy
Hi Jeanine,
Kind regards,
Mynda
priya
A4=A2-A1, If(A4>30,”YES”,”No”). Does it work or not?
Catalin Bombea
Hi Priya,
If you have this in a single cell: A4=A2-A1, If(A4>30,”YES”,”No”), it will not work.
If you have in cell A4 the formula: =A2-A1, then in any other cell the formula: =If(A4>30,”YES”,”No”), it will work.
Catalin
Vignesh
10
15
20
hi friends any one help me to get correct the formula.
In my excel sheet in row i want to apply this formula.
if the cell contains 10 the formula cell needs to show as 1
if the cell contains 11 to 15 formula cell needs to show as 2
if the cell contains 16 to 20 formula cell needs to show as 3
Catalin Bombea
Hi,
The formula should be:
=IF(A1=10,1,IF(AND(A1>=11,A1<=15),2,IF(AND(A1>=16,A1<=20),3,0))) It will return 0 for any other alues than the ones you indicated Catalin
Michelle
I wonder if you could help me. I am trying to set up a spreadsheet of products we sell, however some items have GST (Australian tax at 10%) some do not. My C6 column asks if the product has GST and I have a Y or N answer in this field. Further along my sheet in J6 I have a sale price column. Then K6 is GST (=SUM(J6*1.1-J6))
L6 is sale price plus GST (=SUM(J6+K6))
How could I write in Excel language… If C6 equals N to leave the K6 as Zero, but if C6 = Y to show the equation I already have? Maybe there is a much easier way and I am complicating things too much!
I have Googled myself crazy trying to work this out! Hope anyone could help!!
Mynda Treacy
Hi Michelle,
Try this for your GST calculation:
=IF(C6=”N”, 0, J6*.1)
If that’s not it please post your question and sample file to our Excel Forum.
Mynda
Marcie Heinz
What is the function written out for this problem?
In cell B10, enter a formula using the IF and AND functions to indicate whether the revenue goal has been met that month:
Enter the logical test using the AND function to determine if the Fundraisers amount in cell B7equals 0 and the Total in cell B8 is greater than 20000.
If the logical test is true, display Yes (using “Yes” for the value_if_true argument).
If the logical test is false, insert a nested IF function.
Enter the logical test of the nested IF function using the AND function to determine if the Fundraisers amount in cell B7 is greater than 0 and the Total in cell B8 is greater than 200000.
If the logical test for the nested IF function is true, display Yes (using “Yes” for the value_if_true argument).
If the logical test is false, display No (using “No” for the value_if_false argument).
Catalin Bombea
Hi Marcie,
Please upload a sample file on our forum (open a new topic), with your data structure and a sample result, it’s not clear what you need to do.
Catalin
Alec Gerlach
In cell H7, insert a nested function that awards the employee a 10% bonus if they were hired before 7/31/2012, a 6% bonus if they were hired on or before 7/31/2016, and 4% to employees hired after 7/31/2016.
-can someone help me set up this function? if so, can they kinda explain how they got their answer too? im struggling with this!!!!!!!
Catalin Bombea
Hi Alec,
An Index-Match formula should work:
=INDEX({0.10,0.6,0.4},MATCH(A1,{0,41121,42582},1))
Te formula can be inserted in any cell you need, just replace the A1 reference with the cell that holds the date to evaluate.
Those 2 arrays with constants (in curly brackets) contains your indications: 0.10 will be returned if the date is between 0 and 41121, 0.6 will be returne if the date is between 41121 and 42582, 0.4 for dates higher than 42562.
Make sure you have dates in correct format, not text strings. (a date is a number representing the number of days from 01/1/1900, 41121 is 7/31/2012)
The key is the last argument of the Match function; 1 is the Match type: Less Than.
Alec Gerlach
Thans!!!!!
Dheeraj
Very good
Renee
Need Help with a formula….
If J2 is > K2 up to 100, the answer is 2 but
If J2 is > K of 101 or more, the answer is 1 or
if J2 is < K, the answer is 3
Mynda Treacy
Hi Renee,
Try this:
arjun rawat
Name M1 M2 Project Online Total Percent Status
Arjun 44 46 60 202 352 70.4
Sital 27 38 73 182 320 64
Chandan 40 47 80 275 442 88.4
Mahesh 44 37 73 241 395 79
Madan 33 27 70 235 365 73
Pawan 43 47 91 225 406 81.2
Dinesh 36 37 60 163 296 59.2
Yogendra 26 34 51 218 329 65.8
Aman 41 47 75 244 407 81.4
Nabin 40 48 72 176 336 67.2 ,
i am typing this formula=IF(AND(C3>=25,D3>=25,E3>=60,F3>=150),”PASS”,”FAIL”) for status but it says formula contains error. Could you please locate my error?
Catalin Bombea
You have to upload a sample file on our forum(create a new topic), the formula is looking good. We have to see the file to understand what happens there.
Catalin
Diane Awbrey
This didn’t work.
=IF(ISBLANK(M2),””,IF((TODAY()-I2)>45,”Contact Requester”,””))
It showed Contact Requestor even though M2 had a closed date and when I deleted the closed date it didn’t perform IF((TODAY()-I2)>45,”Contact Requester”,””)) part of the formula.
Mynda Treacy
Hi Diane,
I think I’m confused 🙂 Can you please post your question and sample file on our Excel Forum. That is the best place for support as we can actually see your file and what you’re working with.
Thanks,
Mynda
Diane Awbrey
In reference to my earlier comment….
This almost does what I need
=IF(AND(NOT(ISBLANK(M2))),””,IF((TODAY()-I2)>45,”Contact Requester”,””)), but if the formula is in A1 (such as a template) because I2 is blank A1 Displays “Contact Requester” until I put in an issue date, whereas I want it to be blank.
Mynda Treacy
Hi Diane,
Try this:
Mynda
Diane Awbrey
Hi I am having trouble with my formula., If M2 is blank I want to perform the following **calculation below. This part works great, what I am having trouble with is the cell M2 part of it. If M2 is not blank I don’t need to determine is today is more than 45 days (I2 contains an issue date), however if M2 is blank I need to know if it is overdue (M2 contains closed date)
** =IF((TODAY()-I2)>45,”Contact Requester”,””) works great
How do I write the formula to accomplish if I2 is over 45 days and M2 is blank the cell will populate with Contact Requestor (this part works), but if M2 has a date I want A1 (cell where formula is) to stay blank.
Amanda
I can’t get my formula correct. What I need to do is say “if G3 is greater than today, then write RED in column D3 which is the RAG status column.
Thanks for your help.
Amanda
Mynda Treacy
Hi Amanda,
Have you tried this in cell D3:
If that doesn’t work please upload an example to our Excel Forum so we can help you.
Mynda
Shivam
if grossprofit <=15000,then taxes = gross profit * 20 %, rest all cases Gross profit * 30%
Catalin Bombea
Try:
=IF(GrossProfit<=15000,GrossProfit*0.2, GrossProfit*0.3) You already written the formula, in a very close format 🙂 Replace GrossProfit with a cell reference.
Samer Saadeddin
Hello Guys,
I have a formula that requires 6 out of 12 conditions to be met randomly. if I try to use the IF with AND and OR, I need to write more conditions than the Excel can provide.
the questions is, how can I tell excel that if 6 conditions are met, then the return value would be “X”?
Samer Saadeddin
Correction:
How can I tell Excel to return s specific value if 6 out of the 12 conditions are met randomly?
Mynda Treacy
Hi Samer,
Please post your question on our Excel Forum with a sample Excel file so we can see your question in context, as I don’t understand the scenario you describe.
Thanks,
Mynda
Ybo Tawano
=IF(B28=””,””,IF(OR(B28=B29,B28<B29),"MET","UNMET"))
if B28 and B29 are equal, it's supposed to say "MET" but it doesn't. say B28 and B29 are both 10%, it says "UNMET" instead. care to comment? thanks
Catalin Bombea
Check B28 and B29, there may be more decimals than displayed. Increase the number of decimals displayed, this way you will be able to see if the results are different, or round the values: ROUND(B28,4)=ROUND(B29,4)
Leslie
I need help with this formula.
If column A is “corn” then it’s d5/2.6, but if column A is “soybeans” then it’s d5/.93
also this one:
if column A is “cotton” and column B is “Monsanto” then it’s d5/5.55, but if column A is “cotton” and column B is “Phytogen” then it’s d5/5.11
Any help would be appreciated 🙂
Catalin Bombea
Hi Leslie,
Try this one: =IF(A5=”corn”,D5/0.93,IF(AND(A5=”cotton”,B5=”Monsanto”),D5/5.55,IF(AND(A5=”cotton”,B5=”Phytogen”),D5/5.11,0)))
It will return 0 if no condition is met.
Catalin
Jay
Hi There
Can one do an auto allocation?
If A1 is > than 0 then C1 will always be 4
Thanks
Catalin Bombea
What should be there if A1 is not >0?
Put this in C1:
=IF(A1>0,4,A1)
If you wanted to modify cell C1 from a formula located in another cell, this is not possible, a formula will display a result only in the cell where it’s located. Only through a specific macro you can modify any cell you want.
Kevin
I want =IF(J4>L4,I4,K4) but if J3=L3 I want it to display TIE
Catalin Bombea
Try:
=IF(J3=L3,”TIE”,IF(J4>L4,I4,K4))