I need advice re: excel formula please

longforgotten

So what I want to do is this..........


If cell A1 has the word expense in it and if cell B1 shows a negative figure in it , say -112, I want the minus sign removed from cell B1 to just then show 112


Any help will be appreciated

unforeseen

In plain english
If A1 contains expense then B1 = ABS(B1)

You don't need to test for negative values if the A1 condition is met

that

just multiply it by -1

unforeseen

I think it may need some VBA code actually. I haven't touched VBA for about 10 years

Cornucopia

If the result is to go into a third cell, say, C1, then it's easy...

=IF(LOWER(A1)="expenses",ABS(B1),B1)

I'd add LOWER so that any case matches.

victor2

longforgotten wrote: »

So what I want to do is this..........


If cell A1 has the word expense in it and if cell B1 shows a negative figure in it , say -112, I want the minus sign removed from cell B1 to just then show 112


Any help will be appreciated

You can do it with conditional formatting on cell B1, along the lines of:
Format values where this formula is true: =FIND("expense",A1)
Format: 0.00;0.00


Note this only alters the way the negative number is displayed, if you refer to the cell with another formula, its true value (if negative) will be used.
Also, it is case sensitive, you'd need a slightly more complex condition to get round that.


Why are expense items entered as negative anyway, or are they looked up from elsewhere?
If so, conditional formatting may not be the answer.

Cornucopia

Even with such a simple requirement, more info is required...

unforeseen

Cornucopia wrote: »

If the result is to go into a third cell, say, C1, then it's easy...

=IF(LOWER(A1)="expenses",ABS(B1),B1)

I'd add LOWER so that any case matches.

That will fail if there is a rogue space or other character in the cell

=IF(FIND("expenses",LOWER(A1))>0, ABS(B1),B1)
Deals with there being more than the word expenses in A1

Heedtheadvice

Cornucopia wrote: »

Even with such a simple requirement, more info is required...

Spot on Corn!


The question needs expanding. In Excel thereare many ways to change cell b1depending on the contents of cell a1 but the best solution will be subject to the need/reason xuch as:


Do you wish to change just what is diplayed?
Do you use cell b1 in other calculations i.e. The actual value needs changing?
Is it just a one off cell change or will the test need repeating after a cell change/worksheet open/by user intervention?
Is the change for a single cell based upon that corresponding single cell or multiples of one or the other or both i.e. ranges of cells contiguous or otherwise.


I guess many of us like Corn. believe there is more to it than the outlined need otherwise you could just read cell a1 and change b1 manually!



It would help if you could add more to your original post as to what and why on how it will be used

victor2

longforgotten hasn't been back to look at the replies thus far anyway...;)

unforeseen

victor2 wrote: »

longforgotten hasn't been back to look at the replies thus far anyway...;)

They may live up to their username.