Using “If cell contains #N/A” as a formula condition.
Input the following formula in C1: =IF(ISNA(A1),B1,A1*B1) Screenshots: When #N/A: When not #N/A: Let us know if this helps.
excel-formula
Getting unique values in Excel by using formulas only
Ok, I have two ideas for you. Hopefully one of them will get you where you need to go. Note that the first one ignores the request to do this as a formula since that solution is not pretty. I figured I make sure the easy way really wouldn’t work for you ;^). Use the … Read more
Ignore Duplicates and Create New List of Unique Values in Excel
Totero’s answer is correct. The link is also very helpful. Basically the formula you need is: B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)) Then press ctrl+shift+enter (or it will not work using a array formula). Two important things to keep in mind here: The complete list is in cells A2:A20, then this formula has to be pasted … Read more
How do I recognize “#VALUE!” in Excel spreadsheets?
Use IFERROR(value, value_if_error)
Get content of a cell given the row and column numbers
You don’t need the CELL() part of your formulas: =INDIRECT(ADDRESS(B1,B2)) or =OFFSET($A$1, B1-1,B2-1) will both work. Note that both INDIRECT and OFFSET are volatile functions. Volatile functions can slow down calculation because they are calculated at every single recalculation.
Excel formula to reference ‘CELL TO THE LEFT’
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
Excel Date to String conversion
=TEXT(A1,”DD/MM/YYYY hh:mm:ss”) (24 hour time) =TEXT(A1,”DD/MM/YYYY hh:mm:ss AM/PM”) (standard time)
Remove leading or trailing spaces in an entire column of data
Quite often the issue is a non-breaking space – CHAR(160) – especially from Web text sources -that CLEAN can’t remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),” “))) Ron de Bruin has an excellent post on tips … Read more
Simple Pivot Table to Count Unique Values
UPDATE: You can do this now automatically with Excel 2013. I’ve created this as a new answer because my previous answer actually solves a slightly different problem. If you have that version, then select your data to create a pivot table, and when you create your table, make sure the option ‘Add this data to … Read more
How can I perform a reverse string search in Excel without using VBA?
This one is tested and does work (based on Brad’s original post): =RIGHT(A1,LEN(A1)-FIND(“|”,SUBSTITUTE(A1,” “,”|”, LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))) If your original strings could contain a pipe “|” character, then replace both in the above with some other character that won’t appear in your source. (I suspect Brad’s original was broken because an unprintable character was removed in … Read more