1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Formula help

Discussion in 'Business Applications' started by AoshiKenshin, Feb 11, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. AoshiKenshin

    AoshiKenshin Thread Starter

    Joined:
    Feb 11, 2013
    Messages:
    5
    I'm trying to find a formula for cell 4 which uses cells 1-3. I will give an example of what cells 1-3 will be and then what I need 4 to be.

    Cell 1=35
    Cell 2=44
    Cell 3=5

    So now I need Cell 4 to equal 4. The formula I need is similar to C1+/-5 (so 30-40) and then C2-40. Or if Cell 2 was 26 then I would need it to be the 30-C2. In both cases I need Cell 4 to say it equals 4 when Cell 2 is both 26 and 44. At the same time though cell 3 won't be fixed so if cell 3 was equal to 6 then cell 4 would equal 3 when cell 2 is 26 or 44. I know this is kind of confusing, I just don't know how to explain it.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    Hi welcome to the forum.
    I'm sure you fully understand what you have and what you need and what you want but the way you've tried to explain it is not clear, at least to me.
    Two things, first which version of excel are you using and why don't you attach a sample file where you fill in the differnt possibilities.
     
  3. AoshiKenshin

    AoshiKenshin Thread Starter

    Joined:
    Feb 11, 2013
    Messages:
    5
    I'm using excel 2010. Basically what I'm saying is if C2>(C1+C3) then I want it to use the formula C2-(C1+C3) and if C2<(C1+C3) then I want it to use the formula (C1-C3)-C2.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    pretty much the formula as you have written

    =IF(C2>(C1+C3),C2-(C1+C3), IF(C2<(C1+C3), (C1-C3)-C2,"value is equal"))
    see A1 in attached spreadsheet

    you have not said what happens if result is equal

    if you include = then the formula is much simpler

    =IF(C2>=(C1+C3),C2-(C1+C3), (C1-C3)-C2)
    see cell F1 in attached spreadsheet
     

    Attached Files:

  5. AoshiKenshin

    AoshiKenshin Thread Starter

    Joined:
    Feb 11, 2013
    Messages:
    5
    Oh sorry. It seems I did forget when they equal each other. Basically using the number examples from my first post (C1=35, C3=5). There will be a range of C1-C3 to C1+C3 so 35-5 to 35+5 (30-40). Whenever C2 equals 30, 40 or any number in between I need the formula to equal 0. Other than that, your formula is working wonders etaf.
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    =IF(AND(C2>=30,C2<=40),0,IF(C2>=(C1+C3),C2-(C1+C3),(C1-C3)-C2))

    see attached cell F4
     

    Attached Files:

  7. AoshiKenshin

    AoshiKenshin Thread Starter

    Joined:
    Feb 11, 2013
    Messages:
    5
    The values of C1 and C3 will be changing so instead of having the first part as =IF(AND(C2>=30,C2<=40),0, can I change it to =IF(AND(C2>=(C1-C3),C2<=(C1+C3)),0, and have it work as intended still?
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    possibly
    AND(C2>=(C1-C3),C2<=(C1+C3)

    can C2 be between those two values ?

    AND is looking at C2 and saying

    is
    C2>=(C1-C3)
    and at the same time is - ie AND
    C2<=(C1+C3)

    and when C2 is true for both of those conditions the answer will be zero
     
  9. AoshiKenshin

    AoshiKenshin Thread Starter

    Joined:
    Feb 11, 2013
    Messages:
    5
    Alright. It seems to be working perfect now. Thank you for your help.
     
  10. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    your welcome (y) thanks for letting us know
    You can mark your own threads solved using the [​IMG] button at the top of the page of the thread in the upper left corner. :)
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1089041

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice