SOLVED

Calculate difference, pos or neg between numbers

Copper Contributor

I'm new to Excel and having a problem figuring this out. Here's a simplified explanation of what I'm trying to do:

In A2 I have 100

In A3 I have 150, an increase of 50

In B3 I want to show the increase or decrease between A2 and A3, in positive or negative numbers.

In B3 I used =A2-A3 and got -50 instead of 50

I've not been able to find the correct formula to show positive or negative change.

 

8 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Broeux 

I you want an increase to be positive and a decrease to be negative, use =A3-A2

If you always want a positive (or zero) result, use =ABS(A3-A2)

@Hans Vogelaar 

Thanks much. I see how that works now. Another question:

In the attached file, for blank row 4 cell C4 now shows -150. How can my formula be modified so this would remain blank until B4 is filled? I appreciate your assistance.

@Broeux 

In C3:

=IF(B3="","",B3-B2)

Fill down.

Thanks, Hans. That works beautifully, though I don't understand it. I inherited a workbook from the prior official handling it for our organization, and he didn't understand these things either! I will have to study and learn to read the formulas. I appreciate your time.

@Broeux 

The formula says: if the value of the cell to the left (B3) is equal to an empty string "", i.e. if B3 is blank, then return an empty string "" so that C3 looks blank. Otherwise, return the difference of B2 and B3.

@Hans Vogelaar 

A very succinct explanation of how the formula reads in a way even a caveman can understand. Would you know where I can find the same for other formulas?

@Broeux 

If you'd like to learn about formulas and functions, see for example Formulas and Functions and the links provided there.

1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Broeux 

I you want an increase to be positive and a decrease to be negative, use =A3-A2

If you always want a positive (or zero) result, use =ABS(A3-A2)

View solution in original post