+ Reply to Thread
Results 1 to 17 of 17

IF AND formula?

  1. 02-12-2024, 11:10 AM #1
    Ladyj07
    Ladyj07 is offline
    Registered User
    Join Date
    11-07-2023
    Location
    Connecticut, USA
    MS-Off Ver
    Microsoft 365 and Office
    Posts
    62

    IF AND formula?

    Hello,

    In the attached spreadsheet I want to look at column A for group "SOO" and then in column D for level (RSD) so in column E change that amount to 16.50 if both arguments are true but if column A still "SOO" but column D has anything besides "(RSD)" then it should be "15.00". I try to do and IF, AND, formula which works but for some reason is affecting the other rows that are not SOO and changing then? I highlight the formula I did in row two as an example of what is happening. Unless I shouldn't be using the IF, AND formula?

    Thank you.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-12-2024, 11:24 AM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,964

    Re: IF AND formula?

    Maybe this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 02-12-2024, 11:38 AM #3
    Ladyj07
    Ladyj07 is offline
    Registered User
    Join Date
    11-07-2023
    Location
    Connecticut, USA
    MS-Off Ver
    Microsoft 365 and Office
    Posts
    62

    Re: IF AND formula?

    I try that before too and is not working. Still changing the pay amount when SOO is not applicable.
    Reply With QuoteRegister To Reply
  4. 02-12-2024, 11:46 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 24H2 64-bit)
    Posts
    88,707

    Re: IF AND formula?

    Try this:

    =IF(AND(A2="SOO",D2<>"(RSD)"),15,IF(AND(A2="SOO",D2="(RSD)"),16.5,0))

    You have quotes in the wrong place and where you don't need them - you need to be careful about that. Change 0 at the end to whatever you want if neither set of criteria are true.
    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.
    Reply With QuoteRegister To Reply
  5. 02-12-2024, 11:51 AM #5
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,968

    Re: IF AND formula?

    Formula will change everything to 15.00 if A<>"SOO" and D<>"(RSD)".
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  6. 02-12-2024, 11:52 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 24H2 64-bit)
    Posts
    88,707

    Re: IF AND formula?

    No, it will return 0, John:

    AliGW on MS365 Beta Channel (Windows 11) 64 bit

    A
    B
    C
    D
    E
    2
    XPP
    89698
    5698
    $0.00
    3
    XPP
    95865
    8978
    $0.00
    4
    XPP
    102032
    12258
    $0.00
    5
    OEA
    108199
    15538
    $0.00
    6
    OEA
    114366
    18818
    $0.00
    7
    OEA
    120533
    22098
    $0.00
    8
    OEA
    126700
    25378
    $0.00
    9
    SOO
    132867
    28658
    17.85
    $15.00
    10
    SOO
    139034
    31938
    17.85
    $15.00
    11
    SOO
    145201
    35218
    $15.00
    12
    SOO
    151368
    38498
    (RSD)
    $16.50
    13
    SOO
    157535
    41778
    $15.00
    14
    SOO
    163702
    45058
    (RSD)
    $16.50
    15
    SOO
    169869
    48338
    $15.00
    16
    SOO
    176036
    51618
    (RSD)
    $16.50
    17
    SOO
    182203
    54898
    (RSD)
    $16.50
    Sheet: Sheet2
    Reply With QuoteRegister To Reply
  7. 02-12-2024, 12:12 PM #7
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,968

    Re: IF AND formula?

    @AliGW: I was referring to a previous formula not yours! posts crossing
    Reply With QuoteRegister To Reply
  8. 02-12-2024, 12:18 PM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 24H2 64-bit)
    Posts
    88,707

    Re: IF AND formula?

    Ah, gotcha!!!
    Reply With QuoteRegister To Reply
  9. 02-12-2024, 12:51 PM #9
    Ladyj07
    Ladyj07 is offline
    Registered User
    Join Date
    11-07-2023
    Location
    Connecticut, USA
    MS-Off Ver
    Microsoft 365 and Office
    Posts
    62

    Re: IF AND formula?

    @AliGW the problem is that whatever value is already in column E when is not OSS is changing to 0.00 when I use your formula. I don't want that to happen. I Only want to change value if is OSS and level has anything different to RSD or if is OSS but has RSD. Everything else should stay the same in column E if these two arguments are not applicable.

    Capture.JPG
    Reply With QuoteRegister To Reply
  10. 02-12-2024, 01:04 PM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 24H2 64-bit)
    Posts
    88,707

    Re: IF AND formula?

    You aren't telling us your full requirements, so it's all guesswork. You are drip feeding tham. You never mentioned what should happen if cells are empty.

    Make a list of ALL possible combinations and what should happen in each case. We can't create a formula with half the details missing.
    Reply With QuoteRegister To Reply
  11. 02-12-2024, 01:32 PM #11
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,968

    Re: IF AND formula?

    whatever value is already in column E
    If you have an existing value in E, you cannot use a formula in E as this will overwrite the value: it is one or the other.
    Reply With QuoteRegister To Reply
  12. 02-12-2024, 01:41 PM #12
    Ladyj07
    Ladyj07 is offline
    Registered User
    Join Date
    11-07-2023
    Location
    Connecticut, USA
    MS-Off Ver
    Microsoft 365 and Office
    Posts
    62

    Re: IF AND formula?

    @John Is there another formula that can do this. I thought "IF' will do it but is there another option?
    Reply With QuoteRegister To Reply
  13. 02-12-2024, 01:47 PM #13
    Ladyj07
    Ladyj07 is offline
    Registered User
    Join Date
    11-07-2023
    Location
    Connecticut, USA
    MS-Off Ver
    Microsoft 365 and Office
    Posts
    62

    Re: IF AND formula?

    @AliWG.
    If column A has SOO and column D is equal to RSD then I want the amount on column E to be change to 16.50. If Column A has SOO but column D has anything besides RSD (Blank or other data) then I want the amount on column E to change to 15.00. Everything else that doesn't apply to these two arguments the value that already exist in column E should stay the same.
    Reply With QuoteRegister To Reply
  14. 02-12-2024, 01:56 PM #14
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: IF AND formula?

    Try:

    =IF(AND(A2="SOO",D2="(RSD)"),16.5,IF(AND(A2="SOO",D2<>"(RSD)"),15,E2))

    copied down.
    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
    Reply With QuoteRegister To Reply
  15. 02-12-2024, 01:59 PM #15
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: IF AND formula?

    or this, where no copy/paste is needed. Just make sure the target cells are EMPTY:


    =MAP(A2:A40,D2:D40,E2:E40,LAMBDA(a,d,e,IF(AND(a="SOO",d="(RSD)"),16.5,IF(AND(a="SOO",d<>"(RSD)"),15,e))))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  16. 02-12-2024, 02:03 PM #16
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,968

    Re: IF AND formula?

    The only way with values in E (to be retained) is VBA unless you put the formula in another (empty) column.

    OR assign other value E values via LOOKUP: for example is "TAE" always 8.00, "XPP" always 16:00 ?
    Last edited by JohnTopley; 02-12-2024 at 02:12 PM.
    Reply With QuoteRegister To Reply
  17. 02-12-2024, 03:22 PM #17
    Ladyj07
    Ladyj07 is offline
    Registered User
    Join Date
    11-07-2023
    Location
    Connecticut, USA
    MS-Off Ver
    Microsoft 365 and Office
    Posts
    62

    Re: IF AND formula?

    Thank you John I decide to follow your advice and do this with VBA. Thank you for helping me out with the code. I really appreciate it.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. find formula for (formula)+(*1.5 if colum A has certain word) if not use original formula
    By amac4556 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  2. Copy Value String text of Formula become Formula but the formula not automatically active
    By Yovan Wisnu in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  3. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1