• ARRSE have partnered with Armadillo Merino to bring you an ARRSE exclusive, generous discount offer on their full price range.
    To keep you warm with the best of Merino gear, visit www.armadillomerino.co.uk and use the code: NEWARRSE40 at the checkout to get 40% off!
    This superb deal has been generously offered to us by Armadillo Merino and is valid until midnight on the the 28th of February.

MSExcel Help Please.

#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)
 

dpcw

War Hero
#4
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.
Let me google that for you
 
#5
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.
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.

Cheers.
 
#6
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.
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
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)
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!
 
#10
Which version of Excel are you using? Conditional formatting sounds like what you are after.
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.
 
#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!

=IF(COUNTIF(A1:E1,"green")=3,"green",IF(COUNTIF(A1:E1,"red")>=1,"red","amber"))
 

Latest Threads