I'm trying to find out when the first non-zero occurs. To make it even simpler, I reduce it to just the first time a positive number occurs. Still whatever I do I get a #VALUE.
When using the function wizard - it will tell me what the answer will be. I hit [OK] and get the #VALUE! again.
=MATCH(TRUE,A1:D1>0,0)
Excel 2007
Thanks,
Enter that formula as an array formula.
Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.
When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi and welcome to the forum
That is an array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I've had this problem before, here's my solution. Hope it works for you.
=MATCH(TRUE,INDEX($A$1:$D$1>0,0),0)
also this is NOT an array formula
Last edited by drstrings; 09-10-2013 at 04:33 PM.
I've had this problem before, here's my solution. Hope it works for you.
=MATCH(TRUE,INDEX($A$1:$D$1>0,0),0)
Try this array formula entered with CTRL + SHIFT + ENTER
=MATCH(TRUE,ISNUMBER(1/A1:D1),0)
Thank you. I also like the different ways INDEX, ISNUMBER methods.
You're welcome. We appreciate the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks