# Solved: Excel Formula help

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

Not open for further replies.

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.

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.

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. ### etafModerator

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"))

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:

• ###### IF_C1-2-3_etaf.xlsx
File size:
10.1 KB
Views:
39

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. ### etafModerator

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:

• ###### IF_C1-2-3_etaf1.xlsx
File size:
10.3 KB
Views:
31

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. ### etafModerator

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

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

10. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
your welcome 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.

As Seen On