Need help with an excel formula

ChroniC

ChroniC

ChroniC

Soldato
Joined
18 Oct 2002
Posts
10,258
Im trying to enter a formula that calculates a starting value in currency in one cell, and calculates the difference between a second cell with a finishing value . I.e [a1]£100 [b1]150 [c1]+50
[b2]150 [b2]120 [c2]-30

the reason im struggling is its not a simple + or -, and i dont know the statements that controls if its a plus or a minus. For example i tried, and this and i didnt work
=if(a1<b1),then(a1-b1),else(a1+b1)

:p i dont even think excel recognises else, :D
Anyone know a simpler way of calculating the profit or loss from two money values. I'e googled loads with no luck. Thanks.
 
I don't really understand what you're trying to do I'm afraid, but if you're trying to find the form of a conditional 'if', then try this:

=IF(A1<B1,A1-B1,A1+B1)

If you are trying to get to the absolute value of the difference between two numbers (i.e. without any minus signs) then you could use =ABS(A1-B1).

HTH
 
ok sorry, basically
Untitled-2.jpg



I need a sum to calculate +/-
however the problem im having is its not a simple plus or minus equation because if the closing balance is higher than the starting balance it needs to be a + if its lower then its needs to be a minus.

make sense?
 
So you just want to show a "+" or "-" and possibly an "=" in column D based on starting and closing balance?

If so try this in D2 =IF(C2>B2,"+",IF(C2<B2,"-","="))
 
It's easier to do =c2-b2 and then change the number format on the cell to Custom so it displays the + or - symbol

format_cells.JPG


Spot the deliberate mistake; should read +£#,##0.00;-£#,##0.00 in Type - got the pound symbol in the wrong place on the screeny
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom