Results 1 to 15 of 15

Thread: Help: Excel - Complicated Formulae

  1. #1
    Senior Trouble Maker muddyfox470's Avatar
    Join Date
    Jul 2004
    Location
    moving to Suffolk
    Posts
    3,103
    Thanks
    104
    Thanked
    46 times in 39 posts
    • muddyfox470's system
      • Motherboard:
      • Abit I-N73HD
      • CPU:
      • E4500
      • Memory:
      • 4Gb PC6400 Corsair ?
      • Storage:
      • 2 x Seagate 7200.12 500Gb and 1 x Hitachi 7k1000.b 750gb
      • Graphics card(s):
      • Powercolor 4850
      • PSU:
      • Corsair HX520W
      • Case:
      • Silverstone SG-01e
      • Monitor(s):
      • Fujitsu D22W-1
      • Internet:
      • BT Home

    Help: Excel - Complicated Formulae

    I want to create a large workbook tracking my finances, I've pretty much had enough of MS Money. I want it to be pretty much selfupdating etc after I have inputted amounts/numbers.

    To start me off, does anyone know a formulae so that if I enter a column of data and I want to reference it, I can get Excel to look down that column and pick the last entry? (to display it in another worksheet)

    Secondly using this data, after finding it in the column how can I display it on say another worksheet and then ask it to change the colour/font of the displayed text (on this other worksheet). I.e. red (and bold) if the amount is less than £0 and black if it is more than or equal to £0.

    Help would be much appreciated, with the formulae displayed or some hints. I will no doubt want to do more a bit later one, but just want the basics working at the moment

    Cheers

    Ian
    Mac fancier > white macbook base spec .................. CS: muddyfirebang

  2. #2
    ɯʎɔɐɹsɐʌʍ mycarsavw's Avatar
    Join Date
    Feb 2007
    Posts
    4,945
    Thanks
    1,097
    Thanked
    653 times in 482 posts
    • mycarsavw's system
      • Motherboard:
      • P8H77-M Pro
      • CPU:
      • i5 3350P
      • Memory:
      • 16Gb
      • Storage:
      • Lots
      • Graphics card(s):
      • R9 285
      • PSU:
      • HX 620w
      • Case:
      • FD Define Mini
      • Operating System:
      • W10
      • Monitor(s):
      • BenQ G2420HDBL + GL2450HT
      • Internet:
      • Sky

    Re: Help: Excel - Complicated Formulae

    Bung a sum formula on the last possible row of each column, hide the unused cells in between and unhide as you fill them.

    Link (ie press = in the new sheet, and switch to the above sheet) to that formula in your new sheet.

    There has to be an easier way, but that's what I do.

    Red vs black is in the formatting of the cell. Format it to "number" or "currency" and any minus figures will go red.
    |Kata: "Read title as 'fisting'. Not sure why I clicked. Relieved, really."|
    |TAKTAK: "It was so small that mine wouldn't fit into it"|

  3. #3
    sneaks quietly away. schmunk's Avatar
    Join Date
    May 2004
    Location
    Wiki Wiki Wild West side... of Sussex
    Posts
    4,424
    Thanks
    40
    Thanked
    163 times in 121 posts
    • schmunk's system
      • Motherboard:
      • Abit NF7-S v2.0
      • CPU:
      • AMD Athlon-M 2500+
      • Memory:
      • 1GB of Corsair BH-5 and 512MB of something else
      • Storage:
      • 160GB Seagate Barracuda
      • Graphics card(s):
      • ATI Radeon X800Pro, flashed to XT
      • PSU:
      • Hiper Type-M ~400W
      • Case:
      • Antec cheapy
      • Monitor(s):
      • AG Neovo F19 LCD
      • Internet:
      • Virgin Media 4MB/s

    Re: Help: Excel - Complicated Formulae

    I would have thought that a VLOOKUP() function would do what you're looking for.

    As mycarsavw says, use the Format -> Cells menu to change the display of -ve numbers. As an accountant, I couldn't recommend anything other than having them in brackets...
    Last edited by schmunk; 14-09-2007 at 04:15 PM.

  4. Received thanks from:

    mycarsavw (14-09-2007)

  5. #4
    Senior Trouble Maker muddyfox470's Avatar
    Join Date
    Jul 2004
    Location
    moving to Suffolk
    Posts
    3,103
    Thanks
    104
    Thanked
    46 times in 39 posts
    • muddyfox470's system
      • Motherboard:
      • Abit I-N73HD
      • CPU:
      • E4500
      • Memory:
      • 4Gb PC6400 Corsair ?
      • Storage:
      • 2 x Seagate 7200.12 500Gb and 1 x Hitachi 7k1000.b 750gb
      • Graphics card(s):
      • Powercolor 4850
      • PSU:
      • Corsair HX520W
      • Case:
      • Silverstone SG-01e
      • Monitor(s):
      • Fujitsu D22W-1
      • Internet:
      • BT Home

    Re: Help: Excel - Complicated Formulae

    Thanks for your lightening quick reply, I shall definitely use the latter half, but the first bit I want it to be completely automated so I don't think the sum thing will be anything more than a bodged way.

    I was thinking of some sort of "lookup" in the column and comparing the two cells, so if the one cell has no data in it it would choose the cell directly above it (and therefore the last data entry) however I just cannot put this into an excel formula :s I know similar and more complex things can be done, I just cannot recollect how

    Cheers

    Ian

    SCHMUNK: I'll do my research on the VLOOKUP() formula to see how I can implement it
    Mac fancier > white macbook base spec .................. CS: muddyfirebang

  6. #5
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: Help: Excel - Complicated Formulae

    You could try the following, in the first column of the first sheet put the forumula =ROW() next to each filled line

    Then in the sheet where you want to find the last entry, use the formula = VLOOKUP(MAX(Sheet1!A:A),Sheet1A:G,x,FALSE) to find the corresponding last used line.


    The formatting issue can be solved by either a) Using the Currency type (this has an option to display negative figures in red) or b) Conditional Formatting (Format -> Conditional Formatting) and set two conditions, 1 for Greater Than or Equal to 0 and set that to Bold and the 2nd for Less Than 0 and set that to Red.


    EDIT: That'll teach me to take 10 minutes to post!

  7. #6
    Senior Trouble Maker muddyfox470's Avatar
    Join Date
    Jul 2004
    Location
    moving to Suffolk
    Posts
    3,103
    Thanks
    104
    Thanked
    46 times in 39 posts
    • muddyfox470's system
      • Motherboard:
      • Abit I-N73HD
      • CPU:
      • E4500
      • Memory:
      • 4Gb PC6400 Corsair ?
      • Storage:
      • 2 x Seagate 7200.12 500Gb and 1 x Hitachi 7k1000.b 750gb
      • Graphics card(s):
      • Powercolor 4850
      • PSU:
      • Corsair HX520W
      • Case:
      • Silverstone SG-01e
      • Monitor(s):
      • Fujitsu D22W-1
      • Internet:
      • BT Home

    Re: Help: Excel - Complicated Formulae

    @Schmunk Colourful brackets it is then

    Lucio, thanks for your response, sorry for being such a tard but could you take me through what each part of the formula does.
    • VLookup is obviously the operator that carries out the search
    • What is the MAX there for?
    • Sheet1 is obviously my first worksheet, but what does the A:A do ? (does the A:A and A:G just define the search area, i.e. I just want column A, then the second bit picks up all the data from columns A to G to where I want it?)
    • Then I don't understand any of this: Sheet1A:G,x,FALSE


    Cheers

    EDIT: also found this which seems quite helpful for other likeminded idiots: http://www.contextures.com/xlFunctions02.html
    Last edited by muddyfox470; 14-09-2007 at 04:35 PM.
    Mac fancier > white macbook base spec .................. CS: muddyfirebang

  8. #7
    Senior Trouble Maker muddyfox470's Avatar
    Join Date
    Jul 2004
    Location
    moving to Suffolk
    Posts
    3,103
    Thanks
    104
    Thanked
    46 times in 39 posts
    • muddyfox470's system
      • Motherboard:
      • Abit I-N73HD
      • CPU:
      • E4500
      • Memory:
      • 4Gb PC6400 Corsair ?
      • Storage:
      • 2 x Seagate 7200.12 500Gb and 1 x Hitachi 7k1000.b 750gb
      • Graphics card(s):
      • Powercolor 4850
      • PSU:
      • Corsair HX520W
      • Case:
      • Silverstone SG-01e
      • Monitor(s):
      • Fujitsu D22W-1
      • Internet:
      • BT Home

    Re: Help: Excel - Complicated Formulae

    Wahay got it working with that ROW reference Lucio talked about, now is there anything a bit more elegant so I don't need this =ROW() or will I have to stick with it for the time being.

    Cheers Lucio and the rest of you guys, I think I have got to grips with how the formula Lucio has posted works.

    Will keep you updated if i need further help

    Ian
    Mac fancier > white macbook base spec .................. CS: muddyfirebang

  9. #8
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: Help: Excel - Complicated Formulae

    Sorry, I forgot to explain

    The MAX function finds the highest number, thus in this case you're telling the VLOOKUP to search for the highest result which is the last row filled in.

    The Sheet1!A:A is how Excel writes out ranges, in this case Sheet1 being the default name for the 1st worksheet and the A:A means a range starting at Column A and finishing at Column A.

    the X is the column count in the range (e.g. if you select A:G) that's 7 columns wide, if you wanted a result in the 5th column, X would be set to 5

    FALSE is a flag in the VLOOKUP formula to make sure it only looks for the exact result. Otherwise it tries to find the closest fit (generally not wanted behaviour in a lookup)

  10. #9
    ɯʎɔɐɹsɐʌʍ mycarsavw's Avatar
    Join Date
    Feb 2007
    Posts
    4,945
    Thanks
    1,097
    Thanked
    653 times in 482 posts
    • mycarsavw's system
      • Motherboard:
      • P8H77-M Pro
      • CPU:
      • i5 3350P
      • Memory:
      • 16Gb
      • Storage:
      • Lots
      • Graphics card(s):
      • R9 285
      • PSU:
      • HX 620w
      • Case:
      • FD Define Mini
      • Operating System:
      • W10
      • Monitor(s):
      • BenQ G2420HDBL + GL2450HT
      • Internet:
      • Sky

    Re: Help: Excel - Complicated Formulae

    Quote Originally Posted by schmunk View Post
    I would have thought that a VLOOKUP() function would do what you're looking for.

    As mycarsavw says, use the Format -> Cells menu to change the display of -ve numbers. As an accountant, I couldn't recommend anything other than having them in brackets...
    The latter bit I agree with completely, on a monochrome printer red vs black is pretty much redundant.

    I've learnt something here though, I'll be using the VLOOKUP thing myself.
    |Kata: "Read title as 'fisting'. Not sure why I clicked. Relieved, really."|
    |TAKTAK: "It was so small that mine wouldn't fit into it"|

  11. #10
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: Help: Excel - Complicated Formulae

    Have to say out of all the functions I use reguarly in my data crunching, the most useful to master are VLOOKUP, IF and SUMIF.

    The trick is all of them is to find a unique way to refer to each set of data, often the simplest solution is to combine multiple columns into one reference cell via the use of A1&B1 etc

  12. #11
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    Re: Help: Excel - Complicated Formulae

    with that vlookup, you could just write it as :

    =VLOOKUP(65000, SHEET1A:G, X)

    logic here is that are looking for the highest possible row number. By specifying 65000, it will round DOWN to the closest possible value, and bring the corresponding data across. To use this logic, must avoid using FALSE since that would only work for an exact match

    oh, and I expect you won't be using all 65xxx rows on a spreadsheet, thus my choice of the number.



    edit: oh, forgot to mention, this assumes your first column has a series of ascending values

  13. #12
    Senior Trouble Maker muddyfox470's Avatar
    Join Date
    Jul 2004
    Location
    moving to Suffolk
    Posts
    3,103
    Thanks
    104
    Thanked
    46 times in 39 posts
    • muddyfox470's system
      • Motherboard:
      • Abit I-N73HD
      • CPU:
      • E4500
      • Memory:
      • 4Gb PC6400 Corsair ?
      • Storage:
      • 2 x Seagate 7200.12 500Gb and 1 x Hitachi 7k1000.b 750gb
      • Graphics card(s):
      • Powercolor 4850
      • PSU:
      • Corsair HX520W
      • Case:
      • Silverstone SG-01e
      • Monitor(s):
      • Fujitsu D22W-1
      • Internet:
      • BT Home

    Re: Help: Excel - Complicated Formulae

    Fuddum, So say I wanted to looking in column 5 (E) for the last value in that column, how could I find it using your vlookup formula? Bearing in mind that the column will contain random numbers both positive and negative?

    Or is Lucio's method still the easiest?

    Cheers

    Ian
    Mac fancier > white macbook base spec .................. CS: muddyfirebang

  14. #13
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: Help: Excel - Complicated Formulae

    Either one works, there's a slight arguement in favour of avoiding "magic number" approaches but tbh it's purely a coding thing and not really an issue for someone's home Excel sheet.

    Hmmm, now I'm at home and had chance to play properly, a more elegant method comes to mind.


    =INDIRECT(ADDRESS(COUNTA(B:B),2,1,1))

    To split that down

    INDIRECT - Return the value that's inside the cell address I'm giving
    ADDRESS - Makes a cell reference: Row,Column,Whether it's an Absolute reference,style of reference
    COUNTA - Counts all non-empty cells in a range

    This will work PROVIDING you have a solid line of entries, and can include a header row (or rows) as long as you don't merge any cells together. Your COUNTA is put over the column of data you want to analyse and you can set the Column reference number to ANY column (normally set to the same column as your COUNTA is looking at). This is useful because if you had two columns of data, of which only one had an entry on each line, you could still get the data in the other column.

    Also, this method is more flexible if you wanted for example to get the last 5 rows, you'd simply subtract from the COUNTA part of the address formula

    E.g - To get the third from last row, you'd put =INDIRECT(ADDRESS((COUNTA(B:B)-3),2,1,1))
    Last edited by Lucio; 15-09-2007 at 07:01 AM.

  15. #14
    Senior Amoeba iranu's Avatar
    Join Date
    Oct 2003
    Location
    On the dinner table. Blechh!
    Posts
    3,532
    Thanks
    111
    Thanked
    156 times in 106 posts
    • iranu's system
      • Motherboard:
      • Asus Maximus Gene VI
      • CPU:
      • 4670K @4.3Ghz
      • Memory:
      • 8Gb Samsung Green
      • Storage:
      • 1x 256Gb Samsung 830 SSD 2x640gb HGST raid 0
      • Graphics card(s):
      • MSI R9 390
      • PSU:
      • Corsair HX620W Modular
      • Case:
      • Cooler Master Silencio 352
      • Operating System:
      • Win 7 ultimate 64 bit
      • Monitor(s):
      • 23" DELL Ultrasharp U2312HM
      • Internet:
      • 16mb broadband

    Re: Help: Excel - Complicated Formulae

    One of my fave excel finds is the "conditional sum wizard" (such a cool name). Whilst it is not directly related to your question you may find it handy.
    "Reality is what it is, not what you want it to be." Frank Zappa. ----------- "The invisible and the non-existent look very much alike." Huang Po.----------- "A drowsy line of wasted time bathes my open mind", - Ride.

  16. #15
    Senior Trouble Maker muddyfox470's Avatar
    Join Date
    Jul 2004
    Location
    moving to Suffolk
    Posts
    3,103
    Thanks
    104
    Thanked
    46 times in 39 posts
    • muddyfox470's system
      • Motherboard:
      • Abit I-N73HD
      • CPU:
      • E4500
      • Memory:
      • 4Gb PC6400 Corsair ?
      • Storage:
      • 2 x Seagate 7200.12 500Gb and 1 x Hitachi 7k1000.b 750gb
      • Graphics card(s):
      • Powercolor 4850
      • PSU:
      • Corsair HX520W
      • Case:
      • Silverstone SG-01e
      • Monitor(s):
      • Fujitsu D22W-1
      • Internet:
      • BT Home

    Re: Help: Excel - Complicated Formulae

    Cheers to all of you.

    Thanks for explaining what the formula does as it makes it easier for me to use and commit to memory rather than just blindly using it And at least the formula is a bit easier to use as well it's amazing how simple these things seem after you know about them

    Ian
    Last edited by muddyfox470; 15-09-2007 at 01:46 PM.
    Mac fancier > white macbook base spec .................. CS: muddyfirebang

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Find and Replace
    By kidzer in forum Software
    Replies: 6
    Last Post: 22-07-2007, 03:26 PM
  2. Excel + Database + Automated
    By Kezzer in forum Software
    Replies: 4
    Last Post: 29-03-2007, 04:54 PM
  3. Excel windows
    By Steve in forum Software
    Replies: 4
    Last Post: 25-07-2006, 12:44 PM
  4. Replies: 6
    Last Post: 12-08-2005, 08:50 AM
  5. Explaining Complicated Technicals...
    By TiG in forum General Discussion
    Replies: 2
    Last Post: 13-09-2004, 04:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •