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 Help Needed!

  • 30-04-2008 10:02am
    #1
    orlamcnevin
    Closed Accounts Posts: 29 ✭


    Hi,

    I'm trying to figure out how to sum a row but also creating a rule where the result cannot exceed 37.5.

    ie:

    Mon 7.5
    Tues 7.5
    Wed 7.5
    Thurs 10.5
    Fri 7.5
    Basic Hours 37.5 (cannot exceed this figure)

    So you might be wondering why a sum us necessary at all...

    It may be the case that it would look like this...

    Mon 0
    Tues 0
    Wed 7.5
    Thurs 7.5
    Fri 7.5
    Basic Hours 22.5



    Hope all that makes sense!!

    PS: I have a bet with someone in work about this so if I get the answer I'll win a big!

    PPS: This isn't cheating is it!!


    Thanks,
    Orla


Comments

  • #2
    orlamcnevin
    Closed Accounts Posts: 29 ✭orlamcnevin


    Hi,

    I'm trying to figure out how to sum a row but also creating a rule where the result cannot exceed 37.5.

    ie:

    Mon Tues Wed Thurs Fri Basic Hours
    7.5 7.5 10.5 7.5 7.5 37.5 (cannot exceed this figure)

    So you might be wondering why a sum us necessary at all...

    It may be the case that it would look like this...

    Mon Tues Wed Thurs Fri Basic Hours
    0 0 7.5 7.5 7.5 22.5


    Hope all that makes sense!!

    PS: I have a bet with someone in work about this so if I get the answer I'll win a bet!

    PPS: Yeah so what if this is cheating!!


  • #3
    jimbozo
    Closed Accounts Posts: 80 ✭✭jimbozo


    You will have to specify what you wish to happen if the figures being added exceed 37.5.

    Should the answer to display as 37.5 regardless?
    Would you like another cell to display true/false if 37.5 has/has not been exceeded?

    In any case it will be easy. Just say what you want to happen.


  • #4
    [Deleted User]
    Posts: 18,962 ✭✭✭✭ [Deleted User]


    if(sum(a1:a5)>37.5,37.5,sum(a1:a5))


  • #5
    Biro
    Closed Accounts Posts: 4,091 ✭✭✭Biro


    Sorry, not getting what you want here. You want it to total the hours worked (presumably) from Monday through Friday, and output the result. But the result can only reach a maximum of 37.5 is it?
    So if the above example reaches 40.5 the output the result as 37.5, and if it's less, output the true result?


  • #6
    orlamcnevin
    Closed Accounts Posts: 29 ✭orlamcnevin


    That's it in a nutshell!!


  • Advertisement
  • #7
    orlamcnevin
    Closed Accounts Posts: 29 ✭orlamcnevin


    That's it!!!

    Thanks a million!!


  • #8
    rrpc
    Closed Accounts Posts: 9,244 ✭✭✭rrpc


    You need to be more specific. When the sum exceeds 37.5, what do you want Excel to do?: Change one of the amounts?, pop up a message alert?, Explode?


  • #9
    Biro
    Closed Accounts Posts: 4,091 ✭✭✭Biro


    OK, so I'm assuming that it will ignore the surplus hours. In that case, this formula will work.
    I'll assume that cell A1 to E1 has the headings of Monday to Friday, and A2 to E2 has the values, so I'll use A2 to E2 in my example to calculate the 5 values.
    =IF(SUM(A2:E2)>37.5,37.5,(SUM(A2:E2)))

    Try it and let me know.


Advertisement