Solved: Excel Formula help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 

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.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
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.
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
 

Attachments

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.
 

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?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
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
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
your welcome (y) thanks for letting us know
You can mark your own threads solved using the
button at the top of the page of the thread in the upper left corner.
:)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top