Results 1 to 4 of 4
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 Posts

    Re: excel (office 2000)

    A formula like this in a column referring to Q?
    =max(0,Q7-8)

    To just get the sum (I think) you want:
    =sumif(q1:Q100,">8") - 8*countif(q1:Q100,">8")

    Steve

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Star Lounger
    Join Date
    Feb 2002
    Location
    Illinois, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel (office 2000)

    Try this:

    If ((Q7>=8),Q7-8,0)

  4. #3
    2 Star Lounger rjbphillipsiii's Avatar
    Join Date
    Mar 2003
    Location
    Memphis, Tennessee, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel (office 2000)

    Just a quick thanks, all I had to do was put the = sign in front of what you sent and it works great, Thanks so much <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    R.J.B. Phillips III
    Logical Systems Inc. Part Time Drafter
    IBC WONDER HOSTESS Retired 33+ years
    Firestone Tr & Rubber Company 15 Years rjbphillipsiii@yahoo.com

  5. #4
    2 Star Lounger rjbphillipsiii's Avatar
    Join Date
    Mar 2003
    Location
    Memphis, Tennessee, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula for conditional subtraction (Excel 2000)

    (Subject edited by HansV to make it more descriptive than "Excel")

    I need help on looking at a cell (numeric value), then if it is a value of 8 or more subtract 8 and place the results in the a cell
    but if the value in the cell is less than 8 do not do anything. When I tried this I am getting a -8 in the results and it then makes
    the sum of the column in correct. I am trying to total a group of numbers if they are greater than 8.
    Example: cell value 10 minus 8 =2. but cell value 0 the results I want is null or "0" zero
    cell value 11.5 minus 8 = 3.5 and so on. then the cell doing the =sum(range of cells) would give me 5.5 in this
    small example. I do hope you can follow what I am trying to do ?
    SOME THING LIKE Q7 HAS A VALUE OF 5 THEN I WOULD WANT THE FORMULA IN Q8 TO IGNORE THE 5 AND THE RESULTS WOULD
    BE A ZERO.
    IF Q7 < 8 THEN Q8 = 0 ELSE Q8 = Q7-8
    Check if Q7 Cell is less than 8 if so do not calculate at all, but if q7 is 8 or above calculate and show the difference between
    Q7 if 8 or above in Cell Q8 ?
    I am not sure if something like this is even possible, but this is what I would like to do.
    R.J.B. Phillips III
    Logical Systems Inc. Part Time Drafter
    IBC WONDER HOSTESS Retired 33+ years
    Firestone Tr & Rubber Company 15 Years rjbphillipsiii@yahoo.com

Posting Permissions

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