| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding if error to a formula
Hello, I'm currently using this formula, which works perfectly :
=INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0)) I know to enter ctrl-shift-enter. However if I have not entered data yet for one of the fields the formula is referring to I get: #N/A. I would like the cell to be blank if the reference cells are blank. Thank you |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding if error to a formula
Hi Jim
One way =IF(COUNTA(D1:D2)<2,"",your_formula) -- Regards Roger Govier "Jim" wrote in message ... Hello, I'm currently using this formula, which works perfectly : =INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0)) I know to enter ctrl-shift-enter. However if I have not entered data yet for one of the fields the formula is referring to I get: #N/A. I would like the cell to be blank if the reference cells are blank. Thank you |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding if error to a formula
That did not work. Any other suggestions. Oh, and thanks for helping me.
"Roger Govier" wrote: Hi Jim One way =IF(COUNTA(D1:D2)<2,"",your_formula) -- Regards Roger Govier "Jim" wrote in message ... Hello, I'm currently using this formula, which works perfectly : =INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0)) I know to enter ctrl-shift-enter. However if I have not entered data yet for one of the fields the formula is referring to I get: #N/A. I would like the cell to be blank if the reference cells are blank. Thank you |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding if error to a formula
How did it not work?
It looks like it should. Jim wrote: That did not work. Any other suggestions. Oh, and thanks for helping me. "Roger Govier" wrote: Hi Jim One way =IF(COUNTA(D1:D2)<2,"",your_formula) -- Regards Roger Govier "Jim" wrote in message ... Hello, I'm currently using this formula, which works perfectly : =INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0)) I know to enter ctrl-shift-enter. However if I have not entered data yet for one of the fields the formula is referring to I get: #N/A. I would like the cell to be blank if the reference cells are blank. Thank you -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding if error to a formula
getting the same error. Still ctrl-shift-enter, right?
"Dave Peterson" wrote: How did it not work? It looks like it should. Jim wrote: That did not work. Any other suggestions. Oh, and thanks for helping me. "Roger Govier" wrote: Hi Jim One way =IF(COUNTA(D1:D2)<2,"",your_formula) -- Regards Roger Govier "Jim" wrote in message ... Hello, I'm currently using this formula, which works perfectly : =INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0)) I know to enter ctrl-shift-enter. However if I have not entered data yet for one of the fields the formula is referring to I get: #N/A. I would like the cell to be blank if the reference cells are blank. Thank you -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding if error to a formula
Yep.
But the error could be caused by not finding a match--not because you haven't entered the data. =IF(COUNTA(D1:D2)<2,"not enough data", IF(ISNA(MATCH(1,(Calls_Received_All!B1:B10000=D2) *(Calls_Received_All!C1:C10000=D1),0)),"no match", INDEX(Calls_Received_All!D:D, MATCH(1,(Calls_Received_All!B1:B10000=D2) *(Calls_Received_All!C1:C10000=D1),0)))) Still an array formula. When you get it working, change both those strings to "" (if you want). Jim wrote: getting the same error. Still ctrl-shift-enter, right? "Dave Peterson" wrote: How did it not work? It looks like it should. Jim wrote: That did not work. Any other suggestions. Oh, and thanks for helping me. "Roger Govier" wrote: Hi Jim One way =IF(COUNTA(D1:D2)<2,"",your_formula) -- Regards Roger Govier "Jim" wrote in message ... Hello, I'm currently using this formula, which works perfectly : =INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0)) I know to enter ctrl-shift-enter. However if I have not entered data yet for one of the fields the formula is referring to I get: #N/A. I would like the cell to be blank if the reference cells are blank. Thank you -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding if error to a formula
That was perfect - thanks
"Dave Peterson" wrote: Yep. But the error could be caused by not finding a match--not because you haven't entered the data. =IF(COUNTA(D1:D2)<2,"not enough data", IF(ISNA(MATCH(1,(Calls_Received_All!B1:B10000=D2) *(Calls_Received_All!C1:C10000=D1),0)),"no match", INDEX(Calls_Received_All!D:D, MATCH(1,(Calls_Received_All!B1:B10000=D2) *(Calls_Received_All!C1:C10000=D1),0)))) Still an array formula. When you get it working, change both those strings to "" (if you want). Jim wrote: getting the same error. Still ctrl-shift-enter, right? "Dave Peterson" wrote: How did it not work? It looks like it should. Jim wrote: That did not work. Any other suggestions. Oh, and thanks for helping me. "Roger Govier" wrote: Hi Jim One way =IF(COUNTA(D1:D2)<2,"",your_formula) -- Regards Roger Govier "Jim" wrote in message ... Hello, I'm currently using this formula, which works perfectly : =INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0)) I know to enter ctrl-shift-enter. However if I have not entered data yet for one of the fields the formula is referring to I get: #N/A. I would like the cell to be blank if the reference cells are blank. Thank you -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error adding up times in HH:MM format | Excel Discussion (Misc queries) | |||
| Adding individual error bars | Charts and Charting in Excel | |||
| Sum error when adding 31 days | Excel Discussion (Misc queries) | |||
| Error message when adding a name | Charts and Charting in Excel | |||
| #VALUE! error adding cells | Excel Discussion (Misc queries) | |||
Linear Mode
