|
10 Jan 17 - 07:28 AM (#3831651) Subject: Excel spreadsheets From: Mo the caller 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 |
|
10 Jan 17 - 07:32 AM (#3831652) Subject: RE: Excel spreadsheets From: DMcG There is an AND function but not an AND operator, so you have tonwrite it as AND (x,y) |
|
10 Jan 17 - 08:21 AM (#3831660) Subject: RE: Excel spreadsheets From: Mo the caller "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. |
|
10 Jan 17 - 08:48 AM (#3831667) Subject: RE: Excel spreadsheets From: DMcG IF (AND ($AN="ii", $Cn="b"), formula- $En, formula) Or something like that - i dont have excel here! |
|
10 Jan 17 - 09:43 AM (#3831683) Subject: RE: Excel spreadsheets From: Mo the caller Thank you. I think it works (when I copy it carefully & get the $ in the right place) The Excel help was most UNhelpful. |
|
10 Jan 17 - 10:40 AM (#3831691) Subject: RE: Excel spreadsheets From: SPB-Cooperator If you are dragging down formulas, it is also useful to have an error trap condition. |
|
10 Jan 17 - 12:01 PM (#3831709) Subject: RE: Excel spreadsheets From: Mr Red 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. |
|
10 Jan 17 - 06:17 PM (#3831787) Subject: RE: Excel spreadsheets From: Mo the caller "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. |
|
11 Jan 17 - 03:23 AM (#3831824) Subject: RE: Excel spreadsheets From: DaveRo 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 |
|
11 Jan 17 - 06:17 AM (#3831867) Subject: RE: Excel spreadsheets From: Roger the Skiffler 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) |
|
11 Jan 17 - 06:32 AM (#3831873) Subject: RE: Excel spreadsheets From: Steve Shaw 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! 😈 |
|
12 Jan 17 - 05:21 AM (#3832022) Subject: RE: Excel spreadsheets From: Mr Red 😊 |
|
12 Jan 17 - 09:55 AM (#3832076) Subject: RE: Excel spreadsheets From: SPB-Cooperator 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. |
|
12 Jan 17 - 12:13 PM (#3832118) Subject: RE: Excel spreadsheets From: Mr Red 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. |
|
13 Jan 17 - 10:24 AM (#3832334) Subject: RE: Excel spreadsheets From: Mrrzy This is what databases are for... kidding! |
|
13 Jan 17 - 10:50 AM (#3832339) Subject: RE: Excel spreadsheets From: Jon Freeman 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. |