Can someone put this into into an excel formula?
If B5 = "Cpl" and I75 = 47 or B5 = "Sdt" and I75 = 47 then cell K75=0, if not then cell = I75 And
If B5 not "Cpl" or "Sdt" And I75 = 59 then cell K75=0, if not then cell =I75
Thanks:
Can someone put this into into an excel formula?
If B5 = "Cpl" and I75 = 47 or B5 = "Sdt" and I75 = 47 then cell K75=0, if not then cell = I75 And
If B5 not "Cpl" or "Sdt" And I75 = 59 then cell K75=0, if not then cell =I75
Thanks:
you can not enter values into other cells with formulas or functions, only the cell you are in, so are there typos in your OP, or are you looking for a Macro/VBA solution here?
OR am I missing something ? (always possible )
Last edited by dredwolf; 10-29-2013 at 12:02 AM.
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
The cell I want the result in is K75. So i need the formula enterered in that cell ro produce a value based on looking up the info that is in cell B5 and I75. Would you like to see the sheet?
I would always like to see the sheet
Here you go.
It is the cell in yellow that I would like the formula in and it is based on the selection chosen in B3.
Ok I'd go something like this (k75):
Formula:Please Login or Register to view this content.
(I think I got it all right, but PLEASE say if I don't)
Hope this helps
EDIT- I think i got the results backwards, should be :
Formula:Please Login or Register to view this content.
I believe..getting a bit tired, sorry
Last edited by dredwolf; 10-29-2013 at 12:32 AM.
I made a small change in your code. Changed Std to Sdt. There is missing a constraint though. When it is Sdt and 47 the total should be 0 not 47.Please Login or Register to view this content.
Ah...darn it....change the first And to an OR....my bad
Formula:Please Login or Register to view this content.
been a long day on a jackhammer, so I am a little off, sorry
No problem. You got me closer. I just got to figure out to set the value in I75 to 0 when the 2 conditions are met.
Well, the last formula I gave you meets the conditions stated ( I believe), but if it's still giving the wrong answer, try separting the or, something like this:
Formula:Please Login or Register to view this content.
Last edited by dredwolf; 10-29-2013 at 01:07 AM. Reason: messed up formula, fixed
Well use the same style logic as for K75, if these conditions met, this else that
The hardest part is the basic logic, after that, making sure it works!...lol
Just a hint for EVERYONE, the more possibilities you can erase in the first logic test (or the second, etc..), the less nested IF's you need to accomplish your goal!
(this doesn't mean nested if's are not needed, just that they can usually be pared down by a large number if you think about the results that produce certain answers.... )
AAaarg...could you upload me a sheet with this ?, I've looked through the logic, and I cannot see the error...need some help...lol
I've highlighted it in blue to show that the condition should not go to zero at that point.
=IF(AND(OR(B5="CPL",B5="SDT"),I75=47),I75,IF(I75=59,I75,0))
Azumi
Last edited by azumi; 10-29-2013 at 02:02 AM.
I think I see whats wrong now, I took your written words and translated it literally, I should have read it closer (I hope this is it...truly ), so lets try this:
Formula:Please Login or Register to view this content.
Please tell me this one is right....lol...
Edit-
the reason the or didn't work, is that if it did = one of them, it could not = the other, so the or returned the TRUE value, which means it gave the 0, insted of the I75 value (59)...,
I am so sorry about this, my logic skills are usually so much better than this
Last edited by dredwolf; 10-29-2013 at 02:12 AM.
Yes! Thank you so much dredwolf!
You are very Welcome !
And Thank You, Thank You, Thank You !!! (for putting up with my denseness tonight)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks