# 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

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

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

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

• 10.1 KB Views: 39

#### AoshiKenshin

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.

#### etaf

Wayne
Moderator
=IF(AND(C2>=30,C2<=40),0,IF(C2>=(C1+C3),C2-(C1+C3),(C1-C3)-C2))

see attached cell F4

#### Attachments

• 10.3 KB Views: 31

#### AoshiKenshin

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

#### AoshiKenshin

Alright. It seems to be working perfect now. Thank you for your help.

#### etaf

Wayne
Moderator
your welcome thanks for letting us know 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.