Sponsored by: Discount Supplements - For an extra 6% off Absolutely Everything - use code: SEPT6

excel help

Author
trainer123
Universe Member
  • Total Posts : 407
  • Reward points: 11621
  • Joined: 2002/05/06 00:23:11
  • Status: offline
2009/07/21 13:04:46 (permalink)

excel help

i've scoured the internet and tried to get this to work but cant.  hoping someone can help out?
 
so far i've got:
=IF(AND(E7>"NOVA0001",E7<"NOVA9999",F7="ASS"),IF(OR(I7="SA3148","SA3199"), "OK", "ERROR"))
 
im trying to say IF E7 is betwwen nova0001 AND nova9999 AND F7 equals ASS, AND I7 IS "SA3148" OR SA3199", "OK" otherwise "ERROR".
 
thanks.
#1

10 Replies Related Threads

    1 Rep Max Designer bodybuilding clothing - built to last. Try our popular Hoodie... The 'Utili-Hood' or our hard wearing Training Vests
    1ManRiot
    Pro-Member
    • Total Posts : 22825
    • Reward points: 8899
    • Joined: 2006/11/05 03:16:41
    • Status: offline
    Re:excel help 2009/07/21 13:32:56 (permalink)
    You are asking Excel if E7 is greater than a textual input, which is logically impossible.

    Think you'll need a SEARCH or FIND statement.

    Unaffiliated. 
    #2
    1ManRiot
    Pro-Member
    • Total Posts : 22825
    • Reward points: 8899
    • Joined: 2006/11/05 03:16:41
    • Status: offline
    Re:excel help 2009/07/21 13:35:11 (permalink)
    Or maybe a TRIM to select just the numerical part of the 'nova' input.

    Unaffiliated. 
    #3
    trainer123
    Universe Member
    • Total Posts : 407
    • Reward points: 11621
    • Joined: 2002/05/06 00:23:11
    • Status: offline
    Re:excel help 2009/07/21 13:39:49 (permalink)
    26

    You are asking Excel if E7 is greater than a textual input, which is logically impossible.



    Thats not true.
     
    if i just use =IF(AND(E7>"NOVA0001",E7<"NOVA9999",F7="ASS"),"OK","ERROR") then the formula works fine. works whether it is true or false
     
    its the OR bit at the end that buggers it up.
     
    cheers.
    #4
    1ManRiot
    Pro-Member
    • Total Posts : 22825
    • Reward points: 8899
    • Joined: 2006/11/05 03:16:41
    • Status: offline
    Re:excel help 2009/07/21 14:12:03 (permalink)
    Fair one!  Sorry mate- I'm no good with Excel!

    Unaffiliated. 
    #5
    Brummylad1981
    Olympian Member
    • Total Posts : 525
    • Reward points: 6751
    • Joined: 2008/07/09 12:23:40
    • Status: offline
    Re:excel help 2009/07/21 15:16:14 (permalink)
    Try this mate

    =(IF(OR(AND(E7>"NOVA0001",E7<"NOVA9999",F7="ASS",I7="SA3148"),I7="SA3149"),"OK","ERROR"))

    #6
    trainer123
    Universe Member
    • Total Posts : 407
    • Reward points: 11621
    • Joined: 2002/05/06 00:23:11
    • Status: offline
    Re:excel help 2009/07/21 15:38:24 (permalink)
    Brummylad1981

    Try this mate

    =(IF(OR(AND(E7>"NOVA0001",E7<"NOVA9999",F7="ASS",I7="SA3148"),I7="SA3149"),"OK","ERROR"))

    thanks mate!
    ah i thought that was it then! you're almost a star!
     
    its still not working quite right.  something to do with the "NOVA" bit as im putting free text in E7 that isnt between NOVA0001 and NOVA9999 and its still returning "OK".  the rest of it is spot on.
     
    so im saying E7 must be between NOVA0001-NOVA9999 AND F7 must equal "ASS" and I7 can be either "SA3149" OR "SA3148".  that make sense?
     
    any ideas?
    #7
    Brummylad1981
    Olympian Member
    • Total Posts : 525
    • Reward points: 6751
    • Joined: 2008/07/09 12:23:40
    • Status: offline
    Re:excel help 2009/07/21 15:51:13 (permalink)
    Have you copy and pasted my formula exactly?

    Mine works..............If I put any random text in E7 it comes out with ERROR and not OK as you are saying?

    Try copy and pasting the formula mate, if you have typed something slightly different it may be giving you an error.
    #8
    trainer123
    Universe Member
    • Total Posts : 407
    • Reward points: 11621
    • Joined: 2002/05/06 00:23:11
    • Status: offline
    Re:excel help 2009/07/21 16:04:21 (permalink)
    sorry mate - your completely right. works spot on now.....i think!!

    really appreciate that. was banging my head against a wall as i knew it could be done!

    consider your good karma quota achieved for the day. lol.

    cheers again.
    #9
    Brummylad1981
    Olympian Member
    • Total Posts : 525
    • Reward points: 6751
    • Joined: 2008/07/09 12:23:40
    • Status: offline
    Re:excel help 2009/07/21 16:10:10 (permalink)
    No probs though it did work!
     
    CHeers
     
    post edited by Brummylad1981 - 2009/07/21 16:12:34
    #10
    cliff_vtr
    Pro-Member
    • Total Posts : 12062
    • Reward points: 8622
    • Joined: 2004/04/29 18:15:01
    • Location: Nr Bournemouth
    • Status: offline
    Re:excel help 2009/07/21 23:49:31 (permalink)
    I'd have done it in VB script, easier to debug

    67.4K BW
    170K Squat
    110K Bench
    225K Deadlift

    My Journal
    #11
    Jump to:
    ©2017 All content is copyright of MuscleTalk.co.uk and its use elsewhere is prohibited. (posting guidelines | privacy | advertise | contact us | supported by)
    © 2017 APG vNext Commercial Version 5.5