I entered this formula in cells and I would assume I would get the same answer, but I didn't and I can't figure out why.
=IF(A2='12-02.xlsb'!$A$2:$A$8323,B2+1)
I got the correct answer in the first cell, which is 116.36, but in the second cell I received "FALSE". I don't think it makes any difference but the 2 cells in question are K2 and K3. BTW, I continue to work on my original question and still haven't figured it out, so if anyone has any ideas, I am all ears.
Last edited by CLIFFEAT14048; 12-21-2020 at 04:56 PM.
Try
=IF(A2='12-02.xlsb'!$A$2:$A$8323,B2+1,"")
'12-02.xlsb'!$A$2:$A$8323 is valid? Or do you have a worksheet named 12-02.xlsb? I suppose you could have an Excel 2/3 workbook renamed with an .xlsb extension, but I thought true Excel 5 and later .XLS as well as .XLS[BMX] files always use worksheet names following the workbook name even in workbooks with a single worksheet.
It makes a lot of difference into which cell a formula like =IF(A2='12-02.xlsb'!$A$2:$A$8323,B2+1) is entered.
You're comparing the single cell, A2, to a 8,322-row by 1-column range of cells. If you intended to check whether the former equals any of the latter, you need another function. What this formula DOES do is use implicit range indexing, meaning entering this formula EXACTLY in cell K2, Excel compares A2 to '12-02.xlsb'!A2. OTOH, if you enter the exact same formula in cell K3, Excel compares A2 to '12-02.xlsb'!A3.
What is the comparison you're trying to accomplish? No, it's not clear from the formula you provided.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. As you are still new here, I have done it for you today.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
1. I dont see a sheet name in that formula?
2. An IF statement generally compares something with a single cell, not a range
3. This is what a formula syntax generally looks like when referencing another WB...
=[PERSONAL.XLSB]Sheet3!$K$8
='[Project Planner.xlsx]ProjectSchedule'!$B$9
Or if that WB is closed...
='C:\xxx\yyy\zzz\[Project Planner.xlsx]ProjectSchedule'!$B$9
4. an IF statement generally has 2 arguments to it...
=IF(some-test,do-this-if-TRUE,do-THAT-if-FALSE)
you only have 2 arguments - you left off the last part
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
Sorry guys, I guess I didn't make myself clear. I wasn't trying to have you find out what the formula actually does, but why am I getting different results when I enter it into 2 different cells in the same sheet. I am attempting to learn Excel and ran into this. I see no difference in the cells as they are both completely empty, with no hidden information or formulas.
with this:
K2=IF(A2='12-02.xlsb'!$A$2:$A$8323,B2+1)
yields a vertical result range (not a single outcome): {116.36;FALSE;FALSE;...;FALSE} with FALSE is where A2:A8323 does not equal A2
therefore, copy K3 to K4, excel think of FALSE (the second result, after 116.36)
anyway, it is bad practice to get a result from IF(range, single result), some time it is correct, some time it is not.
Similar, using LOOKUP a non sorting range, the outcome is not guarantee.
Quang PT
Thanks Quang,
Ahhh...that kinda makes sense. Is it returning a different response because it has ALREADY found what I told it to find? If it is, I still don't understand is why that would make a difference, since it's in a completely different cell. Essentially, I am asking: =IF(A2="A",B2+1), but to look in another workbook. I just want to know why the same formula would change, even though it was entered into a different cell. Is Quang's response correct?
Here's what I am trying to do: I want to see if the info (A) in A2 is found in another workbook, which has 20 different cells with the same info in them, essentially 20 rows with (A) in each cell. I have tried using IF, VLOOKUP, INDEX/MATCH, CHOOSE, etc and still haven't figured it out. I even tried using a helper column, with '-1', '-2', etc. after each letter. These are stocks, which change each day. My original post explains what I am trying to accomplish. I just don't know enough about Excel to figure out how to do what I want to do.
WAIT A MINUTE! If I enter the formula in K3, it will compare A2 to '12-02.xlsb'!A3? Does that mean if I enter it in K8, it will compare A2 to '12-02.xlsb'!A8? I am COMPLETELY lost, as that makes absolutely no sense to me! Why would changing the cell location make a difference in this particular formula?Originally Posted by hrlngrv
It’s because you are using a range. It’s probably not the correct formula for what you want, but you aren’t really telling us what that is.
There are instructions at the top of the page explaining how to attach your sample workbook.
As I said in your PM to me....
I understand you were asking "why", but often the unexpected "why" is due to the "how", thats why I made the observations I did
Thanks AliGW,
What I don't understand is: even IF I used a range, if I enter the same formula in 2 different cells, wouldn't it produce the same result? Here's another stupid question: would it make a difference if I named the range? I would say no, but...
Per your PM to me...
No, I am not saying if you understand "how", it will explain the "why" I am saying that often when things dont work as expected, it is because of "how" it was doneI guess I must just be stupid, along with being ignorant, because I don't understand your message. Are you saying that IF I understand how the formula works, that will explain the WHY? I just bought the book, CTRL + SHIFT + ENTER by Mike Girvin. I'm hoping it will explain formulas and arrays better. Are there any books, videos, etc that you suggest I look at?
I have watched numerous videos on Youtube, along with researching numerous Excel sites, in an attempt to figure out a way to find a solution to my original problem. If you have ANY ideas please let me know. I would prefer to figure it out myself, but at this stage of the game, I am COMPLETELY lost and am willing to take ANY suggestions, hints, or even the answer!
Thanks
Cliff
For instance, not adding $ (to absolute a reference - or not adding it properly - can result in answers that you were not expecting. With the exception of some floating point calcs, I have never seen excel formulas produce the wrong answer - they answer exactly what "you" told them to do...not excel's fault that "you" didnt structure the formula properly lol
I guess I must just be stupid, along with being ignorant, because I don't understand your message. Are you saying that IF I understand how the formula works, that will explain the WHY? I just bought the book, CTRL + SHIFT + ENTER by Mike Girvin. I'm hoping it will explain formulas and arrays better. Are there any books, videos, etc that you suggest I look at?
I have watched numerous videos on Youtube, along with researching numerous Excel sites, in an attempt to figure out a way to find a solution to my original problem. If you have ANY ideas please let me know. I would prefer to figure it out myself, but at this stage of the game, I am COMPLETELY lost and am willing to take ANY suggestions, hints, or even the answer!
Thanks
Cliff
Please tell us what the formula is meant to be doing and provide a sample workbook.
Until you do, we'll be going round in circles.
I presume you did enter it with CTRL+SHIFT+ENTER?
I have tried CSE, but I think my formula is wrong. I don't know if this is against the rules or not, but people are getting wrapped up in what the actually does, instead of what my question was, which is: why would the same formula produce 2 different results in 2 different cells. I am including my files, which hopefully explain what I was trying to accomplish. This started as a learning project, but it's either unsolvable or way beyond my Excel learning level. I thought this would be a good way to learn Excel formulas and for some dumb reason, I can't let it go until I solve it. I have tried several CSE formulas, along with various nested VLOOKUP, INDEX/MATCH, IFS, etc. I can get part of a formula to work, but not the whole thing.
You keep criticising your helpers for the approach they are taking to try to help you: if you knew better than they do, you would not be asking for help here. If you want help, be prepared to learn from them and just answer their questions. If you had done this at the outset, this would probably all be done and dusted by now!
OK I entered your formula in "MAS" tab in K2 :
=IF(A2='[12-01 - TOP - FILE.xlsb]12-01'!$A$2:$A$18,B2+1)
the result is "109.49"
If I drag it down to K3 it becomes:
=IF(A3='[12-01 - TOP - FILE.xlsb]12-01'!$A$2:$A$18,B3+1)
The result is "111.34"
Is this what you mean by different results?
Sorry Belinda, but no. I entered the EXACT SAME formula into K3 and got a TOTALLY DIFFERENT response, which is why I asked the question. In K2, I got "116.36", and in K3, I got "FALSE", which is what I didn't understand. I would've thought I would get the exact answer, no matter what empty cell I put the formula in, but I just don't know enough about Excel to answer my own question. Thank you so much for the response though. I appreciate the response.
Last edited by AliGW; 12-21-2020 at 02:24 AM. Reason: PLEASE don't quote unnecessarily!
OK, can you please upload the file with these formulas entered at K2 &K3?
Sorry if you feel like that Ali, but I don't feel like I am criticizing anyone, or, at least, I hope not. If anyone feels like I am, then I sincerely apologize. When I asked my original question, I only wanted to know why the formula had 2 different outcomes, but it seemed that people were asking me about what the formula actually accomplishes, which is why I added the tables and other info into what I was trying to accomplish in an earlier post. Again, if anyone takes my remarks as criticisms in their attempt to aid me, then I sincerely apologize.
Basically - since your formula is an array it produces actually multiple results (as many as the number of cells in your range)
so, for example the below formula:
=IF(A2='[12-01 - TOP - FILE.xlsb]12-01'!$A$2:$A$18,'[12-01 - TOP - FILE.xlsb]12-01'!$B$2+1)
produces the below 17 results:
{116.36;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Since the cell is able to show only 1 result it is showing 116.36, and the other time FALSE
The way to handle array formulas in order to summarize the result into one number is adding another function like SUM to wrap the array, so in this example:
SUM(--(IF(A2='[12-01 - TOP - FILE.xlsb]12-01'!$A$2:$A$18,'[12-01 - TOP - FILE.xlsb]12-01'!$B$2+1)))
Another useful formula (not necessarily fits in your case) is the aggregate function which retrieves the smallest/largest number within the array....
Last edited by Limor_OP; 12-20-2020 at 05:56 PM.
Thanks Belinda. This is what bebo021999 said, more or less, which I understand. I understand that my formula is using an array that compares A2 to each cell in column A of 12-01 - TOP - FILE and returns the result. What I don't understand is what difference does it make if I enter the formula in K2, D7, M14, etc? You, along with others, may have already answered the question and I am just not getting it.
If I enter the formula in K2 and it finds the correct answer; if I enter the formula in D7, would it make a difference that the formula was already entered in K2?
Last edited by AliGW; 12-21-2020 at 02:23 AM. Reason: PLEASE don't quote unnecessarily!
I have tried using the 'SUM(--(', but I will try it again. I believe you have a very valid clue here, but I think the problem lies in the ways that I am trying to use it. I have a separate file called "Formula", which I use list each formula that I try and the results. Thanks again.
Last edited by AliGW; 12-21-2020 at 02:24 AM. Reason: PLEASE don't quote unnecessarily!
But I asked you in post#20 to upload the file where you have these 2 results...maybe then we can analyze that...
when I type the formula in different cells I do get the same response...
So just upload the file instead of going in circles....
Last edited by Limor_OP; 12-21-2020 at 04:08 AM.
Twenty five posts and no meaningful progress.
Cliffeat, can you please attach samples of ALL relevant files and restate the problem in another post on THIS thread, so we don't have to wade throught this entire string of emails to figure out what's going on. It's already too long to follow clearly...
Failing that, we may have to consider this problem as one of life's great mysteries and close the thread.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Sorry everyone...ENTIRELY my fault. The ONLY difference in the formulas from my original post is the number of the last cell in 12-02. I hope this helps clear things up.
I don't know if this will help, but I think hrlngrv explained this behavior back in post #3 as "implicit intersection." If you did not understand what he was describing (and I don't know how to describe it any better), you might read something like this https://exceljet.net/glossary/implicit-intersection. An internet search for "implicit intersection in excel" returned several other pages with explanations. This is the key to understanding why your formula is giving different results and why the cell the formula is in determines what result is returned.
Thanks Mr.Shorty, that explained it perfectly so even I could understand it, which is EXACTLY what others were telling me. I totally understand why this occurred now. I could've sworn that I tried CSE with this formula but I was wrong because I just tried it and received the same answer: 116.38, which confirmed what you suggested. Thanks again.
See post #16!
Anyway, if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks