[Closed] Excel help! Damn paperwork!

Well you've not thought this through have you!

OK - If it was me (and I'm a mediocre dabbler) in the individual sheets I'd pick an unused col and assign numbers to each answer in that sheet (using IF if required-Yes=1, Yesterday=0 .....).

Then in the "summary" sheet you do an IF for sheet1:Z1=1 AND sheet2:Z1=1... or much much better and if you're clever : do a multiply: 0x9x3 = 0, 1x1x1 = 1 etc and have that inside a formula for Nice/Shite etc..

couldashouldawoulda - Member

Well you've not thought this through have you!

LOL! Been trying but am not good at formula as I hardly used them until today ... arrggghhh ...

OK, will try to code them to see if it works ... arrrghhh ... excel ... 😯

not good at formula as I hardly used them until today ...

Oh - I wonder if this is the right time to learn? Hopefully someone will be along with a more approachable / doable solution...

couldashouldawoulda - Member

Oh - I wonder if this is the right time to learn? Hopefully someone will be along with a more approachable / doable solution...

Never too late to learn. Might just get by tonight but in the long run there should be a better way once I figure out how ... 😀

Cheers

Maybe [url= http://office.microsoft.com/en-us/excel-help/sumif-HP005209292.aspx ]SUMIF[/url] may help you. If not, refer to the [url= http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP005204211.aspx ]function reference documents[/url]. HTH.

P.S. If you are a banker - would appreciate a slice of that bonus you don't want! 🙄

mrgoll - Member

Maybe SUMIF may help you. If not, refer to the function reference documents. HTH.

P.S. If you are a banker - would appreciate a slice of that bonus you don't want!

Thanks for the reference documents ... will try to learn them ...

No banker I am afraid but monkey earning peanuts hence trying to do some lower level paper works for big cheese.

Sorry I could not have been of more help.

Maybe "COUNTIF" is more appropriate in your case.

Good Luck and please excuse my poor excuse for a sense of humor... 🙂

---8<---

Sounds a little like you need to rationalise the problem - by that I mean split it up into smaller chunks.

"One function to do them all" is no go in the computer world - we call it a sivler bullet (impossible).

I suggest you use "total" fields for aggregating the results you have (using one set of functions) and then another set for grouping the results (i.e. was "ok||not ok"). This is standard methodology - because the functions are generally quite specialised.

To be honest I do not fully understand the problem - and that means I cannot really help you to accurately make appropriate choice about the solution.

I do guarantee though that if you learn to read and understand the official documentation - that is the fastest path to mastery.

---8<---

OK. Last word from me. I read the first post again, and I think you probably want to nest your if's. [url= http://office.microsoft.com/en-us/excel-help/if-HP005209118.aspx?CTT=3 ]Give this a try[/url]. In particular example 3 - showing nested IF's.

Good Luck. Learn little bits at a time and build up the complexity bit by bit (I guess your functions will a little look odd to say the least).

🙂

I'm guessing that this is asking the lines of:
Do you buy widgets?
Do you like our widgets?
When do you next intend to buy a widget?

What you want to do is code your responses to numbers. The simplest easy to do this is by using find and replace or filleting the columns and then changing all the Yes values to 1s say by selecting them all and using CTRL Enter after changing one value.

I'd make total Nos or Nevers zeros... Then use vlookup to pull all the tables together (you'll need a common ID for each respondent). Then you can score each one by multiplying the values together. All your absolute No bods will score zero.

Using If would be better, but if you're struggling with formulae...

You could try something like:

=CONCATENATE(Sheet1!A1,Sheet1!B1,Sheet2!C3,Sheet3!D3)

To get all your answers in one column apply some simple if statement like:

if(a1="YesToday","OK", if(a1="NoToday","Not OK", "other")) etc

Depends how many variations in answers you have.

Thanks folks ... I will keep trying to get them right. 🙂

Have re-read the question a couple of times and still not sure I understand your layout, but I'd have thought the multiplication method above makes most sense. If you're able to post the file (or an anonymised version) to Google docs or email (address in profile) I'd happily have a more informed look - sadly this is the kind of thing I do for a living.

MrGreedy,

I Will try to give you some samples when I can ...

Just got a very lousy day today due to failure to comply with some works ... not related to this but still bloody lousy ...

Getting rather tired now ...

😐

Still think concating the answers to a single column is the easiest. From there simple multiple if statements.

Generally you are limited to 7 ifs but like this:

=CONCATENATE(
IF(A1="A","Yes","")
,IF(A1="B","Maybe","")
,IF(A1="C","No","")
)

You could add up to 30. Any more then that and you just do the next 30 in the next column and concat them in the next.

I surprised this is still going - and more surprised that my idea might still be in the running! Along with MrGreedy - if you want to send a sample: I'll see what I can do.

There are just too many ways to skin a cat!