MSExcel Help Please.

Discussion in 'Gaming and Software' started by Horridlittleman, Sep 5, 2010.

Welcome to the Army Rumour Service, ARRSE

The UK's largest and busiest UNofficial military website.

The heart of the site is the forum area, including:

  1. I've been voluntold to create a spreadsheet in work. I've got it pretty squared away but am stuck on a formula to make it do one more thing.

    Basically I have a series of criteria that give a displayed result of red, amber or green (with that text in the cell). I then have a summary cell for the row and I want it to automatically show red, green or amber based on the following:

    If all cells are green return the result green.
    If any cell is red then return the result red.
    If no cells are red but any of the cells are amber then return the result amber.

    Any help would be appreciated.
  2. A bit of clarification to your message - has this last formula to decide the overall result of the criteria? Have you used If..., Then... formula in the spreadsheet?

    Trying to get to grips with what you want to achieve before suggesting a solution.
  3. You could use words in your RAG status cell e.g Red (hidden by the red cell). This would allow you to do an VLookup (linked to a results table). The result would then populate into your summary cell (which would have it's own criteria in it to tern it R,A,G).

    Invoice is in the post :O)
  4. Let me google that for you
  5. It is to decide the overall result. So 5 results of red, amber or green which then need this formula to give the overall result.

  6. Sorry, I have tried IF but keep getting an error (NAME or VALUE) when I have tried it. Unfortunately I haven't got the spreadsheet at home and can't access the interweb from work!
  7. Each of the individual cells do have the words hidden within them so I will try the VLookup option - not one I had heard of.

    Many thanks.

    Metaphorical pint is owed!
  8. msr

    msr LE

    Which version of Excel are you using? Conditional formatting sounds like what you are after.
  9. dpcw,

    Many thanks for the lmgtfy but I wouldn't have found the sight by googling any of the questions I had. Unfortunately the site doesn't have a nice big signpost to what I need. Very useful though and I may well use it in the future.

  10. I think its 2003! Not in work so couldn't say.

    I'm happy with the conditional formatting of the cell (if cell = 'Red' make cell shaded red) but couldn't work out how to make the cell value change to reflect my criteria! Unless the conditional formatting would do it for me.
  11. Create a results table for all the out comes and linked summary.
    Concatenate your RAG result cells which should match one of the out comes from you results table. Do the old Vlookup of Concatenated cell/results table and Bobs your uncle.
  12. You could also try using CountIf to count the number of reds, greens etc, in a range of cells.
  13. Try something like this -


    You migh have to play around with the brackets a bit though
  14. Many thanks for all of the suggestions.

    LVH I am afraid my IT buffoonery left me baffled with your suggestion! MSR with the results from the formula below I can use the conditional formatting to make the cell colours work.

    Buttmaster General I used your suggestion and through some rather inelegant formula creation managed to come up with a solution. If you are really interested please see the result of my Sunday evening geekery below!

  15. Cheers CRS, just done it using BMGs suggestion.