+ Post New Thread
Results 1 to 5 of 5
Office Software Thread, Excel Formula help - in Technical; I've got a great report on Spiceworks that gives detailed information about memory in machines and how many banks are ...
  1. #1
    basicchannel's Avatar
    Join Date
    May 2010
    Location
    Norfolk, UK
    Posts
    283
    Thank Post
    37
    Thanked 24 Times in 16 Posts
    Blog Entries
    1
    Rep Power
    24

    Excel Formula help -

    I've got a great report on Spiceworks that gives detailed information about memory in machines and how many banks are free etc. I've exported this to CSV but what I'd really like is a formula which goes through each entry and calculates how much it'd cost to upgrade according to Crucial.

    Name Model Memory Free Bank Upgrade Cost
    PC1 Optiplex 320 1024 0
    PC2 Optiplex 360 2048 1

    So what I want is a formula that will look at the model, then the memory then the free banks and if all three meet certain conditions it takes data from sheet 2, which contains the data from Crucial.

    So for example If column B = "Optiplex 390" AND column C < 2048 AND column D = 0 THEN insert data from sheet2 Cell XX

    I'm not expecting someone to present me a formula, but if you could point me in the right direction i'd really appreciate it.

    Thanks

  2. #2

    sonofsanta's Avatar
    Join Date
    Dec 2009
    Location
    Lincolnshire, UK
    Posts
    5,372
    Thank Post
    955
    Thanked 1,627 Times in 1,102 Posts
    Blog Entries
    47
    Rep Power
    711
    There is likely a more elegant way of doing it, but you could possibly do it with a series of tables and nested VLOOKUPs referencing them.

  3. #3

    tmcd35's Avatar
    Join Date
    Jul 2005
    Location
    Norfolk
    Posts
    6,053
    Thank Post
    896
    Thanked 1,008 Times in 821 Posts
    Blog Entries
    9
    Rep Power
    349
    Code:
    =IF(C2<2048, IF(D2>0, VLOOKUP(B2,Sheet2!A1:Z360,<Column XX>)),"No upgrade available")
    I think that's roughly what I'd do...
    Last edited by tmcd35; 21st May 2013 at 03:39 PM.

  4. Thanks to tmcd35 from:

    basicchannel (21st May 2013)

  5. #4
    basicchannel's Avatar
    Join Date
    May 2010
    Location
    Norfolk, UK
    Posts
    283
    Thank Post
    37
    Thanked 24 Times in 16 Posts
    Blog Entries
    1
    Rep Power
    24
    Quote Originally Posted by tmcd35 View Post
    Code:
    =IF(C2<2048, IF(D2>0, VLOOKUP(B2,Sheet2!A1:Z360,<Column XX>)),"No upgrade available")
    I think that's roughly what I'd do...
    Ah sweet, that was actually very similar to what I had except I wasn't using the VLOOKUP thing. I'll give this a go

  6. #5


    Join Date
    May 2009
    Posts
    3,382
    Thank Post
    301
    Thanked 914 Times in 682 Posts
    Rep Power
    346
    Often you find these nested if then structures are simple state space matrices. So you can add a column to sheet 1 which is just a string concatenation of the options "Optiplex32010241","Optiplex320102409" etc, and that compound key is a straight lookup into the solution matrix. No If's, Then's or buts.



SHARE:
+ Post New Thread

Similar Threads

  1. Excel formula help
    By timbo343 in forum Office Software
    Replies: 9
    Last Post: 4th May 2012, 04:08 PM
  2. Excel Formula Help
    By timbo343 in forum Office Software
    Replies: 3
    Last Post: 6th May 2010, 10:42 AM
  3. HELP - Excel Formula - HELP
    By princessbucki in forum Office Software
    Replies: 7
    Last Post: 19th March 2010, 05:19 PM
  4. excel formula help again please
    By RabbieBurns in forum Windows
    Replies: 2
    Last Post: 13th August 2008, 06:31 PM
  5. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 05:56 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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