I need advice re: excel formula please

Cornucopia

unforeseen wrote: »

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

Good plan. Or to make it even more flexible:-

=IF(ISERR(FIND("expenses",A1)),B1,ABS(B1))

This "finds" the matching word or not, but never returns an error value (which I assume is what would otherwise happen on Excel, just as it does on Google Sheets).

John_G_Jones

victor2 wrote: »

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.

That isn’t a good way to go if you intend to do any further calculations with it, as the number is still negative, it just shows as positive.

longforgotten

Hi there, I'm back


Right here's the story behind my request...........


Using QuickBooks and I want to create a list of purchase invoices/credit notes posted. Unfortunately in QuickBooks many standard reports that exist in other software just aren't there,, you have to create them.


Anyway, all goes well if I ask for purchase invoices and purchase credits. The problem is that a member of staff has wanted some invoices to be treated as paid by cash, and QuickBooks then treates them as expenses, they don't hit the ledger at all. Ok so I add expenses as an additional request on my report and these then come up as negative figures. I need them to be positive figures.


I don't have excel at home so I intended to have a go at your suggestions tomorrow when I'm back in work


Thank you for all the replies

Heedtheadvice

I now sort of understand you problem - I am assuming you wish to change the data in Excel follosing a report export from Quickbooks.
Yes it can be done in Excel but if you do not want an extra column then VBA is the way to do it, based upon the formula improved above.


However before embarking on that route, as Quickbooks is also powerfull, can it not be done within that program?


I readily admit to not being familiar with it but a search showed up something similar and how to fix it. Perhaps watching it might help? https://m.youtube.com/watch?v=q5779YsQxwk

longforgotten

Thank you Heedtheadvice


I have asked QuickBooks some time ago but no joy I'm afraid. It's that QB online treats these invoices paid by cash as an expense ie in it's eyes a payment. It then wants to treat them as negative figures when I pull the transactions through to the report.


The figures on the report are literally just numbers, not created by a formula. I want a formula that will remove the minus figure if the word within a particular cell is expense


For all those out there using software ... if I was using sage I could get the reports I wanted in a heartbeat.

Heedtheadvice

So,.... (I hate things that start like that!)....do you want (and can you use) vba code to change the negatives to positives in Excel?


If so I can do but cannot promise immediate result this week but would try my best!

longforgotten

Heedtheadvice, thank you so much for your help, it is very kind of you............


I had to look up what 'VBA' means


I would feel awful if you had to spend a lot of time on finding the answer. So thank you for your offer but I will continue with my untidy fudge around this problem.


Who knows if enough people ask QuickBooks for what is a basic report then perhaps they will come up with what is needed

victor2

If you don't want to use VBA and cannot have an extra column, create another spreadsheet that pulls in the data from the Quickbooks exported report and instead of pulling over the value column that needs adjusting, create a formula in it driven by the "expenses" field as suggested above, ao that it uses the absolute (ABS) value of the relevant numeric field.

Bit of extra work whenever you produce the report, but once you've got the template built, it isn't so bad.

WaywardDriver

Use of VBA seems a bit OTT. What exactly do you want to do with the numbers? Produce a report of Expense v. Income? If so should be easy to convert -ve's to +ve's.

Heedtheadvice

Yes, I should have thought along Victor's lines. The double sheet or double workbook with linkages would seem great approach......
....but vba is not really ott, quite simple probably, could just be a function effectively and could even be produced as an add-in so can be added to every Excel workbook that might be used with the reports. Vba need not be complicated at all and not too long to produce and does have advantages.



If victor's solution is not the way forward for you then the offer still stands......might even knock it up for fun as time permits