Stoner – that would be awesome. It is medical data though so I can’t send it all over, so if it is OK, I will make a similar example with some made up data and send it over?

I really appreciate all your help!

I am inputting the conditional format rules into C3, and then copy and pasting the rules to C3:O15

yep – done that.

But then I need to do:

=AND($C$16=1,C3=$P$16)
=AND($C$16=2,C3=$P$16)
=AND($C$16=3,C3=$P$16)
=AND($C$16=4,C3=$P$16)

Then:

=AND($C$17=1,C3=$P$17)
=AND($C$17=2,C3=$P$17)
=AND($C$17=3,C3=$P$17)
=AND($C$17=4,C3=$P$17)

etc etc

Yep – I am just trying to avoid writing them all out one at a time.

It won’t let me either copy or paste the formula(s), which is a little annoying as I could just change the numbers.

Stoner – in a way, yes. But they are typically inputted in a ‘batch’ so a load of 1’s may go in together (say in C15:C20) with an associated value in the P column (P15:P20). So we are matching the number in P15 with a number somewhere in C5:O15 and colouring the cell in C5:O15 relative to the number in C15. One rule for each colour at present.

Stoner – they are simply ‘categories’ in excel terms (they are reagents used in a lab in reality).

Hmmm, I wonder if I am not explaining this last bit correctly – which is likely the case.

Lets try again,

I am asking two questions and if both are ‘Yes’ then I want to highlight the matching cell.

Question 1. Is the number in cell P15 the same as a number in cells C3:O12 [it will be, if that matters]

Question 2. P15 has another cell associated with it (C15), which will contain either 1,2,3 or 4. The number in this cell determines what colour to make the cell in C3:O12. (I have a separate rule for each colour – 1 – red, 2 = yellow, 3 = green, 4 = black)

So right now I have four formulas:

=AND($C$15=1,C3=$P$15) [red]
=AND($C$15=2,C3=$P$15) [yellow]
=AND($C$15=3,C3=$P$15) [green]
=AND($C$15=4,C3=$P$15) [black]

And these format formulas have been copied to all cells in the C3:O12 range of cells.

I am trying to save myself typing the above formulas out for each pair of question cells (i.e., column C and column P).

Is that explained any better?

mefty – thank you! I will try that soon and will report back.

Hello!

Sorry for the delay – I was out of the office for a short while.

lets keep it simple, the present formula is:

=(AND($C$15=1,C3=$P$15) with the ‘C3’ being the range that is being compared to ‘P15’.

What I want to do is then make the same formula but to then compare it to P16 (to compare the numbers) and the ‘C15’ would change to ‘C16’ to check the formatting colour), so it would be:

=(AND($C$16=1,C3=$P$16)

I guess I am simply trying to save myself from writing out 200 conditional format rules (4 rules per comparison, and 50 comparisons).

Stoner (just in case you see this again)…

The conditional formatting rule evolved a little last night to become:

=(AND($C$15=1,C3=$P$15)

[with the underlined ‘1’ being formatted to ‘red’, and another rule being the same but with a ‘2’ and that would be ‘blue’ etc].

Is there an easy way to copy all of these rules and apply then the subsequent cells in the ‘P’ column – so $P$16, $P$17 etc (the rest will all stay exactly the same).

Hope that makes sense?

Stoner – that is brilliant and SO simple – I have been over thinking this a little too much! Thank you.

Stoner, that is ace – it only bloody well works – thanks!

BUT – my problem is a little more complicated than that – just wanted to try and get a simple version to work first.

The column of numbers is actually a few columns long (a ‘range’? Is that the right term?).

So the range of numbers is say A1:D5, and the number that needs comparing is in E1.

I have just tried “A1:D5=$E$1”) and that doesn’t work…?

Again, thanks if you can help?

Stoner… that was quick… thanks… runs off try it out…

Power meters are the future.

cadence x torque = power

What works best for a given individual largely depends upon their muscle fibre composition and the efficiency of the motor unit recruitment patterns developed through training at a given cadence.

It is possible to manipulate the way bioenergetic properties of muscle fibres to make them more effective at different cadences/torques thorough training – but it is a long process.

I wonder if they will add a Quark power meter to the chainset – the ‘ultimate’ singlespeed option!

last bump.

bump

OK, I am struggling, can you guys please help?

Thank you both.

They are great, I have one, but I don’t ride it much at the moment so I am thinking of selling it – medium in raw if you are interested? Email in profile.

http://www.wiggle.co.uk/effetto-carbocut-hacksaw/

druidh/nuke – thanks for your comments!

Seadog – that looks nice!

So a lazer is the way to go then.

Zero titaniums here… in white… perfect.

Craigxxl – great, a real accountant! Thanks for offering your thoughts! I like the sound of what a ‘good accountant’ should do… I just need to find one that knows my business area.

Thanks everyone, really helpful advice so far!

One of the key things for me is that an accountant has to save me more money (as in from things that I do not know about, not the standard deductions) than the cost of the accountant themselves. So if they cost £1000, and they only save me an extra £500, then I am £500 down on where I would have been without an accountant at all – I can fill in the basics of the HMRC website myself with little problem (said, ‘confidently’. haha).

Coolhandluke – thanks for the DIY accounting link. I think I will have a look at one of those spreadsheets, I have one set up already, but seeing how the ‘professionals’ do this sort of thing might be useful.

Bazzar – Thats interesting abut SJD, they seem to be very helpful via there emails and free guides etc. What I want though is an accountant to say that I should do X and Y, and suggest Z if it is appropriate (and more so if I didn’t know about Z). I don;t need my hand holding, but I do need guidance – I am a scientist not an accountant (despite each, liking/loving numbers).

Geoffj – Thanks, I will have a look at Crunch (I actually recall that name, now you mention it).

I have actually spoken to a few local accountants, one stood out – but still, handing over a good few hundred pounds for someone to fill in some forms for me seems a bit steep… but, they may know lots of ways of making it worth my while… you just don’t know till you hand over the cash.

I have also had contact with what appears to be a very good online accountant (SJD accountancy) but they are about £1200 a year. How much do you all pay for your accountants?

Question to all – how do you k ow when you meet a good accountant? What should I be looking out for?

mcobie – if you have/know a good accountant I would be pleased to have their contact details.

Geoffj – I have been thinking about that for a few weeks.

Thanks for all the responses – my business is a little complicated as there are a range of income streams, and it appears that unfortunately, their are also few overheads too and so a high proportion of the turnover is actually profit (I know that is not really a bad thing) – customers pay for my knowledge rather than me buying and selling on a ‘product’ per se.

Yep, all expenses are legitimate – not trying to fiddle anything.

I take it my (small) salary doesn’t count as a business expense? Without factoring in that, I don;t see how you could break even and still afford to live?

bump

Oldgit, if you really want to know where you are, and whether what you are proposing will move you on, or if it is too much and so you need more rest you need to set up a performance manager chart. Drop me an email if you are interested and I can help you.

(I am an exercise physiologist, endurance coach, and in the very near future, also a consultant for the Garmin-Barracuda pro team).

pistonbroke – give me an email, I am an exercise physiologist (and I might be able to help, but I am in Aberystwyth), if not, I know a sports psych guy at Sheffield Hallam that also might be abe to help.

Finished the climbing challenge – maybe a week ago. But, I spent the first three weeks riding in some Spanish mountains, so it was kinda cheating really.

cu dubh – thanks, I have had a look and it costs £30… that is quite a lot to pay… I am a sports scientist and I just want to read it out of curiosity, I am unsure if I will learn anything from an eccentric perspective on training and racing… but you never know.

edit: You are probably right though, it would be good to see a slice of the cash going back to GO.

I haven;’t read it, but I am keen too – so if either of your copies are going to be sitting on a shelf gathering dust I will happily give you some dollar for it?

I have a Macbook pro with Thunderbolt power (I though these were first introduced in 2011?) and a Thunderbolt 27″ Cinema display – it is AWESOME!