Excel spreadsheets

Subject: Excel spreadsheets
From: Mo the caller
Date: 10 Jan 17 - 07:28 AM

I use a spread sheet to keep tabs on what I have in various bank & savings account. I have worked out how to use the IF function to see how interest much my bank accounts should earn, if the balance is less or greater than various tiers. But when interest is paid in I have to alter that cell by hand (which can make problems if I alter something else later).
I've tried to look for an AND function, but can't see one.
Is there some way I can say
If value in
column 1(type of payment) is ii
and column 3(bank account code) is b
then column 7(balance in that account) is formula - value in column 5
otherwise formula.
I suppose I could do it by using extra columns, but there must be a neater way.
I'm using Excel 2007

Subject: RE: Excel spreadsheets
From: DMcG
Date: 10 Jan 17 - 07:32 AM

There is an AND function but not an AND operator, so you have tonwrite it as AND (x,y)

Subject: RE: Excel spreadsheets
From: Mo the caller
Date: 10 Jan 17 - 08:21 AM

"Run that past me again"
If I wanted just one condition I'd write in column G row n
=IF($An="ii",formula-$En,formula)
or
=IF($Cn="b",formula-$En,formula)
So what do I type if I want both conditions to be true.

Please.

Subject: RE: Excel spreadsheets
From: DMcG
Date: 10 Jan 17 - 08:48 AM

IF (AND ($AN="ii", $Cn="b"), formula- $En, formula)


Or something like that - i dont have excel here!

Subject: RE: Excel spreadsheets
From: Mo the caller
Date: 10 Jan 17 - 09:43 AM

Thank you. I think it works (when I copy it carefully & get the $ in the right place)

The Excel help was most UNhelpful.

Subject: RE: Excel spreadsheets
From: SPB-Cooperator
Date: 10 Jan 17 - 10:40 AM

If you are dragging down formulas, it is also useful to have an error trap condition.

Subject: RE: Excel spreadsheets
From: Mr Red
Date: 10 Jan 17 - 12:01 PM

IF(ISERROR(AND ($AN="ii", $Cn="b")),"there is an error",IF (AND ($AN="ii", $Cn="b"), formula- $En, formula))

where N is a number and n is another. ii & b are conditions that mean something. Usually errors are caused by empty cells and arithmetic. Or those numbers exceeding the limits (negative or >65,000). Which could be trapped but it gets pretty complex IMNSHO.

well that's how I would do it, or more likely write a macro "Function", declare it in a "Module" and use it in a cell. Are you still following me?

macros can be recorded and used by setting up a button but once you start conditional logic it becomes a programming exercise, you have to need the result to go that route.

Now if you click on the "fx" text in the formula bar you get a dialog that you can search for functions in categories. Doing it to a formula will help you work out elements in the formula. Highlighting a part can help on just the part. I still have to scan to get the function I want, it is logical, but not in my head necessarily.

Best of luck. Feel free to PM me - I may be able to highlight something.

Subject: RE: Excel spreadsheets
From: Mo the caller
Date: 10 Jan 17 - 06:17 PM

"Are you still following me?" I lost you on the first line.

Not that bothered really, but I just thought there must be some way to do and as well as if.

Thanks.

Subject: RE: Excel spreadsheets
From: DaveRo
Date: 11 Jan 17 - 03:23 AM

It's not clear to me what exactly how your interest is calculated

If the interest rate depends only on the balance and applies to the whole balance I would use a VLOOKUP function to get the rate. You set up a small 2-column table of balances and rates (usually on another sheet) and use an 'approximate match' to find the rate corresponding to the balance. LOOKUP functions are hard to get the hang of but really useful in lots of circumstances. If the rates change, or tiers are added, the interest formula does not change.

If different rates apply to portions of the balance I would add more columms, each with a calculated portion, and a single formula to calculate and sum the interest on each.

You could use both methods together - extra columns with tiers and rates in a table. Depends how often these change.

Personally I would avoid nested IFs. Too Easy to make mistakes.

HTH

Subject: RE: Excel spreadsheets
From: Roger the Skiffler
Date: 11 Jan 17 - 06:17 AM

Aaaaaaah!
When I retired I wanted a T-shirt that read " Retired: I never have to do another *%$@~ Excel spreadsheet ever again".
I had a boss who even if I had to give her a single figure statistic had to have it on a separate spreadsheet!
RtS
(Rant over , Blood pressure going down)

Subject: RE: Excel spreadsheets
From: Steve Shaw
Date: 11 Jan 17 - 06:32 AM

Just send me all your bank details, sort codes and PIN numbers, Mo, and I'll sort it all out for you in no time! 😈

Subject: RE: Excel spreadsheets
From: Mr Red
Date: 12 Jan 17 - 05:21 AM

😊

Subject: RE: Excel spreadsheets
From: SPB-Cooperator
Date: 12 Jan 17 - 09:55 AM

I usually also have a trap for blank rows so that if a formula is dragged beyond the last row (may do this for future-proofing) if their is no data then the dragged down cells show no info.

Subject: RE: Excel spreadsheets
From: Mr Red
Date: 12 Jan 17 - 12:13 PM

I sometimes allow cells to show error because formatting the cell to show nothing when processing an error may not show there is no formula. Which means you can forget how far the formulae go down. And blithly extend the data set past the row that ends the formulae.

Using a visible character instead of blank does complicate the formula, which is harder to debug as you change the data they are handling - eg numeric/non-numeric. In essence you can forget the original thinking/purpose of cells.

Keep it simple.

Subject: RE: Excel spreadsheets
From: Mrrzy
Date: 13 Jan 17 - 10:24 AM

This is what databases are for... kidding!

Subject: RE: Excel spreadsheets
From: Jon Freeman
Date: 13 Jan 17 - 10:50 AM

I think databases are for when spreadsheets get too big...

Well not quite and its a long time since I used this one but (much as I dislke MS) MS Access can lend itself to simple solutions. Others here including DaveMcG would be more likely to give better pointers as to what is best for what than I can although I think I'd be trying a spreadsheet for the opening post question.