How to use "if"function to check 15 criteria or Is there any alternative formula for this.plz help
2007-12-24 03:41:36 · 7 answers · asked by sarvy 4 in Computers & Internet ➔ Software
Look at the 'lookup' commands in Excel. They check lists or tables of values.
2007-12-24 03:45:09 · answer #1 · answered by DesktopSupport.TV 3 · 0⤊ 0⤋
A pivot table would be the simplest, but you want a formula so OK. First the pivot table. Highlight all of the cells that contain data, including the header information. Go to Data>Pivot Table. When the dialog box opens, Click Finish. The Pivot Table Field list will open. Drag the data to the far right column and to the data field. This will be automatically set to count. It will give you a count and a summary of each down the left column. Right click on the top left gray button and choose Field Settings. A dialog will open allowing you to adjust for your needs. Anytime you add new data, you will need to refresh the pivot table by right clicking anywhere on the table and select Refresh. AutoFilter: Select the cell at the top of your list and click Data>Filter>Auto Filter. A drop down box will appear. Click on the arrow and select the filter criteria "New Dehli", no quotes. Look at the bottom left corner of your screen. It will say "x of xx records found". This will give you a quick count. Formula: Select and empty cell and type =countif(a1:a1000,"New Dehli") This will count all of the records that have the exact text "New Dehli", no quotes, in them. You will need to select several cells to place this formula in. Replace "New Dehli" with "Agra", etc. as needed. This can be done on your summary tab. It does not have to be on the same work sheet.
2016-05-26 03:08:37 · answer #2 · answered by ? 3 · 0⤊ 0⤋
Hi,
The maximum number of conditions that can exist within a single IF statement is 13. However, you can chain IF formulas so that an IF in one cell can depend upon an IF in another cell, which effectively gets around the nesting limitations.
-Jim Gordon
Microsoft Mac MVP
MVPs are independent and do not work for Microsoft
http://mvp.support.microsoft.com/.
2007-12-24 06:06:47 · answer #3 · answered by jimgmacmvp 7 · 1⤊ 0⤋
your question needs to be clearer. anyway, to use the "IF" function in Excel, you start the formula with "=" followed by the "IF" command, then open bracket "(". the condition to test for, the value if condition is met and the value if condition fails. eg. =IF(C5=>70%,"Pass","fail") this example means that if the value in cell C5 is greater than or equal to 70%, excel should return the value pass in the active/current cell other wise, fail. you can only use the if function for a true or false expression. hope this helps
+
2007-12-24 04:05:51 · answer #4 · answered by Topstar 1 · 0⤊ 1⤋
Well when it was too much for the cell to handle i used other cells that i didnt need some where else in the table.
As far as i could read the question, it was asking if there was a LONG IF statements ie, If(x>y,if(y>z,if(v>i,"NO",if(H>X,0,1),"Maybe),"Ok"),"Whatever")
but a DAM long statment
2007-12-24 03:45:17 · answer #5 · answered by angellahash 3 · 0⤊ 0⤋
From what I could gather from your query, you wish to use more than 7 if conditions in the same formula.
I don't want to go into the array bit since there is plenty of info about it online. Below is an alternative approach
if(a1="condition1",
This should ideally let you use as many if's as you want.
Hope this helps.
2007-12-26 23:05:38 · answer #6 · answered by Anonymous · 0⤊ 0⤋
15 is too many, if statements max out at 7
turn your data into a 15 row and 2 column table and use a vlookup
2007-12-24 04:58:09 · answer #7 · answered by expletive_xom 7 · 0⤊ 0⤋