Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Excel IF query

  • 04-03-2009 1:51pm
    #1
    andyell
    Closed Accounts Posts: 17 ✭


    Have a calculation that is not working correctly

    =IF(A4<50>=499,R4/0.6,IF(A4<999>500,R4/0.55,IF(A4<2499>=1000,R4/0.5,IF(A4<4999>=2500,R4/0.4,IF(A4<9999>=10000,R4/0.35,IF(A4<9999>=50000,R4/0.25))))))

    the only part of the statement working is the fist section

    Any help would be gratefully received

    Andy


Comments

  • #2
    TheBazman
    Registered Users, Registered Users 2 Posts: 6,462 ✭✭✭TheBazman


    Without thinking too much about it it looks like the formula is a bit off. The first part is saying that if 50 is both greater than A4 and greater than or equal to 499, then ........ However 50 is never going to be greater than or equal to 499. Can you type out exactly what you are trying to do? I think you need to use the AND function a bit more, something like
    = IF(AND(x>A4,A4>=y),TRUE,FALSE) ???


  • #3
    Thoie
    Registered Users, Registered Users 2 Posts: 6,344 ✭✭✭Thoie


    Agree with TheBazman

    The format needs to be
    =IF(AND(A4>49,A4<500),R4/0.6,IF(AND(A4>499,A4<1000),R4/0.55,IF......)

    Note the changes of numbers, as Excel makes it difficult to do >= and <=

    So you want things greater than 49 but less than 500 (captures 50-499), then greater than 499 but less than 1000 (captures 500-999).

    If the ranges are likely to change from time to time, I'd put them somewhere else and reference them, so you can change them easily. Also consider in this case, what happens if A4 is less than 50 or greater than 50,000?


  • #4
    andyell
    Closed Accounts Posts: 17 ✭andyell


    Finally had it worked it out and hopefully this might help someone else

    Thanks for the feedback

    =IF(A4="","",IF(AND(A4>=50,A4<=499),R4/0.6,IF(AND(A4>=500,A4<=999),R4/0.55,IF(AND(A4>=1000,A4<=2499),R4/0.5,IF(AND(A4>=2500,A4<=4999),R4/0.4,IF(AND(A4>=5000,A4<=9999),R4/0.35,IF
    (AND(A4>=10000,A4<=49999),R4/0.25)))))))

    A4="","", is put at the beginnig of the calculate so that if the cell is empty nothing appears

    Thanks for your help

    Andy


Advertisement