If statement
Question
Hello Mr. Smith,
I have data in a master table and I'm trying to summarize the data in a separate sheet by including only entries in one column which meet specific criteria. Each cell within this column can contain one of four values: WON, ACTIVE, PENDING or LOST (I've used the list feature in the data validation menu here). What I want to do is list all of the WON entries in one section of a separate table and all of the ACTIVE entries in another section. I'd also like the information in corresponding rows to be displayed. I hope this makes sense (???)
Answer
I don't think it's an if statement - IF you need to get what is effectively a filtered list across, you will need to use an array filter - http://www.aidanheritage.byethost3.com/excel/Array_Filter.xls has an example - if you need help with this do let me know - my email if it helps is aidan.heritage@virgin.net. NOTE taht I found this question in the question pool which meant that for some reason the original expert was not able to answer the question.
Question
Hi Richard I hope you can help with this.
I am struggling to get an if statement to work, basically I have 3 criteria the first is anything below 80% needs to return ¡°Poor¡±, the second anything between 80% - 85% needs to return ¡°Average¡± and the last is anything above 85% needs to return ¡°Good¡±.
I have tried in vain for hours now using all different configurations, below is the one I have eventually come to and although I can get ¡°Good¡± or ¡°Poor¡± from it I just can¡¯t get value ¡°Average¡± to show:
=IF(F18>80%,"Poor",IF(F19=80%<85%,"Average",IF(F19<85%,"Good")))
I am fairly new to using IF statements and am sure that I have got it wrong in the second criteria I just done know what I have got wrong.
Any help would be greatly appreciated
Many thanks Steven
Answer
Stsven
Try this
=+IF(F18<80%,"Poor",+IF(F18>=85%,"Good",+IF(AND(F18>80%,F18<85%),"Average","")))
You should be able to copy and paste the formula directly to your worksheet.
Multiple if statements are always a bit difficult to get exactly right, a comma here or a paren there makes a big difference.
The main problems with your formula was You needed an IFAND formula to do the greater than 80% but less than 85% portion. You made references to both F18 and F19 in your formula --I don't know if this was intentional, but I took the liberty of changing all of them to F18. So that the formula disn't leave out 85% you needed the greater than or equal to symbol >=.
Other than that it was great.
Question
Trying to write this formula but can¡¯t get it to work, see below
If column ¡°B¡± has a N/A then put a 0 in column ¡°C¡± if ¡°B¡± does not equal N/A populate ¡°C¡± with whatever is in column ¡°A¡± Amount Ending Balance Total -4,668.91 3508.23 -0.42 8177.14 -818.48 N/A -8,128.44 8177.56 +2,349.91 16306.0
Answer
Sounds like it should be:
C1: =IF(B1="N/A",0,A1)
Easy enough?
You can use the IF function to haveplace a value in a cell based on one or more conditions or values in other cells.
See this FREE tutorial on my web site for more information on how to use the IF function:
http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExperts
Question
i am trying to get a formula that looks at 2 figures decides wat the percentage difference is and returns a third percentage as the result
this is wat i have so far
=IF(I8="","",IF(I6<=i8,0.8%,3%,IF(I6<=i8,100%,4%,IF(I6<=i8,120%,5%))))
what i need is for the formula to look at the budget amount (i6) decide wat % of the budget has been achieved (entered in to cell i8) and then if its greater than or equal to either 80% 100% or over 120% and then return either 3 4 or 5% depending which is true
so if budget is 20k and the result achieved is 16k then thats 80% so the 3% answer is returned and so on
hope ive explained this ok
Regards
Marcus
Answer
I think this is what you are looking for:
=IF(I8="","",IF(I8/I6<=0.8,3%,IF(I8/I6>=1.2,5%,4%)))
The first IF takes care of the 80% and under test and the second IF test for 120% and above. If both of those test fail, then the result is between 80% and 120% and 4% is returned.
Question
Can you help me for the following with formula or macro: I want a cell to pick up value from another cell based on the condition that the there is a true argument with the third cell. For example, I want to have value in "A7" equal to value of "C7" only if value in A1 = yes otherwise i want user to fill the data in the cell A7. (Cell "A1" is having the drop down menu with the options yes or no) Can i do the same with "if" formula as we need to enter the value_if_false. I want cell "A7" to be left blank when the argument that A1 is not equal to Yes so that user can fill the data.
Answer
Nik
You can have the cell appear to be blank, but it will still have your formula in it.
in cell A7 put the formula:
=if(A1="yes",C7,"")
is the formula. If the user types in a value in that cell, the formula will be destroyed and only the value the user entered will remain.
So a cell can have either a formula or a constant value in it (or be truly blank/empty).
the above formula is the closest one can come to what you describe. If you truly want the cell to be empty when A1 <> "yes", the you would need to write a macro for that.
Question
Dear Could you pls help me on the following: Assume you have data(Unique %26 duplicated Values) in column A2, I a function to provide me those data frequency as shown (Column B2): A2 B2 100 1 100 2 101 1 101 2 101 3 200 1 203 1 203 2
Thank you very much Bassam Khwailah Jordanian working in KSA
or
00966560312723
Answer
Bassam
This is a little tricky because the you are counting the occurrences of the value in column A in a range that increases in number of rows as you go down the column. I believe the following formula in column be will replicate the results you have shown. I wouldn't call this a "fequency, except maybe the highest count for each value in column A; which you could get with COUNTIF and referencing the entire range.
=COUNTIF(OFFSET($A$1,0,0,ROW(),1),A1)
Question
My question is, i need an if statement to assign 4 different numbers if the number in the reference cell is greater then or less then a certain number. (00-39, 40-74, 75-94, 95-99)
Answer
You could use a complex IF statement, but you might be better off with a VLOOKUP statement. Check out this tutorial:
You can use the VLOOKUP or HLOOKUP function.
VLOOKUP is a powerful function in Microsoftthat allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table).
See this FREE tutorial for help with VLOOKUP:
Advertisement