The problem is that I can’t find a way of converting the logic 1’s or 0’s resulting from the IF formulas used in F13, 20 and 25 to a value where I can compare the results of the aforementioned cells and display OP, SOP or WI in C27 which corresponds to the table in columns L to O.
Any guidance on how to solve my spreadsheet problem or suggest a better method of achieving the same result would be much appreciated.
I cant tell what you are trying to do LOL
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
try this array formula:
Formula:
Please Login or Register to view this content.
(ctrl+shift+enter)
eliminates the need for all your helper stuff.
Hope I could help - if your post has been solved don't forget to mark it as such.
Clicking the * below is a great way to say thanks!
"Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito
Hi there. You could use this array formula to do the job. They're a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly braces yourself - it won't work...
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
Just to back MikeTRON up, how does it correspond?
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Simarui, nice. i'd been fiddling with removing the the helper stuff, too, just for fun and came up with this one... Since you posted yours, here's mine...(see c27)
The main problem that I see: You have a =CONCATENATE() function in G27 that returns the text string "110". The IF() function in C27 is comparing the text string "110" in G27 to the number 110. I don't know how all programming languages handle this sort of thing, but Excel treats text strings and numbers as if they are different. So, the text string "110" is not equal to the number 110. I think the first key principle to understand and decide on is whether you want to make these comparisons as text strings or as numbers. I don't know that it matters either way, but you must decide and then be consistent.
From there I would probably use an approach like what Glenn Kennedy is suggesting (though I prefer not to use the single cell array formula that does everything in one cell approach). You have the beginnings of a lookup table in columns L:O. I would probably work this section of the spreadsheet up into a good lookup table (it might only need an additional column to concatenate L, M, and N together), then C27 will be a relatively simple lookup function. https://support.office.com/en-us/art...__toc309306714
Originally Posted by shg
Thanks for taking the time to reply to my post. For some reason I can't download the spreadsheets that have been posted, therefore apologies if my query has already been solved within!
MrShorty, regarding a preference of which approach to take, I have none. It is how it is at present due to my limited knowledge of formulas. Whichever works is good enough for me. However, I did try your suggestion of concatenating and using a lookup but was unable to figure out how I could compare G27 (the concatenated result of the 3 sections A,B & C) which is variable depending on the answers given, to the lookup table in cells Q10:R16 then display the result in cell C27.
Any suggestions?
P.S. Just realised I can't upload my updated spreadsheetTherefore FYI the lookuptable in Q10:R16 was basically a concatenated version of the table in L11:O16.
did you see my post?
Hi simarui and apologies for not mentioning your reply.
I'd prefer to have the formula work automatically rather than CTRL-SHIFT-ENTER each time. However, I went to try your solution anyway but failed at the first hurdle!
Where and how do you place this array within the spreadsheet?
if you were unable to download my proposed solution (this has been a common problem for some people here, recently) send me a Private Message with your email address and I'll email it to you.
G
Tried to PM you but don't think it worked either
Last edited by heliskier89; 04-24-2015 at 06:20 PM.
OK. Got it. I'll mail it ASAP. I'm off for a beer now. I strongly recommend that you edit your previous post to remove your email address. Spam, spam, spam, etc.
G
the formula will work automatically after you enter it - just have to hold ctrl+shift when pressing enter after pasting that formula in a cell. It doesn't matter which cell you put it in, but it seems like you wanted it in C27. Just copy the formula, click on any cell, paste, then hold ctrl+shift and press enter
In C27:
OR (no helpers)
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by protonLeah; 04-26-2015 at 01:24 AM.
Ben Van Johnson
Re: USING LOGIC 1's AND O's WITH FORMULAS
Thanks to all who offered solutions, it is much appreciated. As always there is more than one way to achieve the same end result. I ended up persevering with the CONCATENATION of the results from A, B and C answers, then created a VLOOKUP table with a value and OP, SOP or WI to corresspond with each combination.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks