Hi There
I was wondering if there is a way to have a currency number be positive or negative based on a another cell?
for example
sumif = "expenses" then the number displays as negitive (-1000)
sumif = "income then number would display as positive (1000) with out the nimus?
Sure. Do you mean there is a cell with a string that says either "income" or "expense"? Let's say that string is in A1. You have a bunch of numbers that you are summing in A2:A100.
=IF(A1="expense",-1,1)*SUM(A2:A100)
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
its a bit more complicated then that. can you take a look at the excel sheet and see the highlighted areas? i basically want that when i type in "expense that when i type in any digit in the amount it automatically makes it negative and when i chose "income" that when i type in the amount it shows positive
Because your dollar amounts are just typed in, there two ways to do this. One is to have one column where you enter the data (say P), and column F will contain a formula like the one I provided that refers to the data you entered.
=IF(C2="Expense",-1,1)*P2
The other way that is more elegant is with a macro that detects any time you change a value in column C or F, and then it updates the sign of the number in column F accordingly. I have attached that solution.
Keep in mind that this macro is specific to a worksheet, and it looks like you're going to add a new worksheet for each month. Therefore this macro has to be copied to each new worksheet.
Im sorry to bother you again, can you show me in a sheet how its done? i dont quite get it and the one you attached doesn't have the formula in it many thanks
The one I attached has a macro in it. When you select Income or Expense in column C, the number in column F is automatically updated to be positive or negative. If this does not work for you then you probably have your security settings so high that macros will not run.
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks