Lyrics & Knowledge Personal Pages Record Shop Auction Links Radio & Media Kids Membership Help
The Mudcat Cafesonny

Post to this Thread - Sort Descending - Printer Friendly - Home


Excel spreadsheets

Mo the caller 10 Jan 17 - 07:28 AM
DMcG 10 Jan 17 - 07:32 AM
Mo the caller 10 Jan 17 - 08:21 AM
DMcG 10 Jan 17 - 08:48 AM
Mo the caller 10 Jan 17 - 09:43 AM
SPB-Cooperator 10 Jan 17 - 10:40 AM
Mr Red 10 Jan 17 - 12:01 PM
Mo the caller 10 Jan 17 - 06:17 PM
DaveRo 11 Jan 17 - 03:23 AM
Roger the Skiffler 11 Jan 17 - 06:17 AM
Steve Shaw 11 Jan 17 - 06:32 AM
Mr Red 12 Jan 17 - 05:21 AM
SPB-Cooperator 12 Jan 17 - 09:55 AM
Mr Red 12 Jan 17 - 12:13 PM
Mrrzy 13 Jan 17 - 10:24 AM
Jon Freeman 13 Jan 17 - 10:50 AM
Share Thread
more
Lyrics & Knowledge Search [Advanced]
DT  Forum
Sort (Forum) by:relevance date
DT Lyrics:



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


Post - Top - Home - Printer Friendly - Translate

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)


Post - Top - Home - Printer Friendly - Translate

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.


Post - Top - Home - Printer Friendly - Translate

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!


Post - Top - Home - Printer Friendly - Translate

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.


Post - Top - Home - Printer Friendly - Translate

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.


Post - Top - Home - Printer Friendly - Translate

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.


Post - Top - Home - Printer Friendly - Translate

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.


Post - Top - Home - Printer Friendly - Translate

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


Post - Top - Home - Printer Friendly - Translate

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)


Post - Top - Home - Printer Friendly - Translate

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! 😈


Post - Top - Home - Printer Friendly - Translate

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

😊


Post - Top - Home - Printer Friendly - Translate

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.


Post - Top - Home - Printer Friendly - Translate

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.


Post - Top - Home - Printer Friendly - Translate

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

This is what databases are for... kidding!


Post - Top - Home - Printer Friendly - Translate

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.


Post - Top - Home - Printer Friendly - Translate
  Share Thread:
More...


You must be a member to post in non-music threads. Join here.


You must be a member to post in non-music threads. Join here.



Mudcat time: 26 April 9:04 PM EDT

[ Home ]

All original material is copyright © 1998 by the Mudcat Café Music Foundation, Inc. All photos, music, images, etc. are copyright © by their rightful owners. Every effort is taken to attribute appropriate copyright to images, content, music, etc. We are not a copyright resource.