Excel People

[wykikitoon 20956]

Maybe one for @@Happy Face

I have a spreadsheet with several figures in. I want to input a figure into a cell and then that automatically get divided by another figure. How do I do that?

I.e I input 10 into a cell and it automatically divides it by 2 so when I put 10 in, it shows 5.

[The Fish 10978]

Maybe one for @@Happy Face

 

I have a spreadsheet with several figures in. I want to input a figure into a cell and then that automatically get divided by another figure. How do I do that?

 

I.e I input 10 into a cell and it automatically divides it by 2 so when I put 10 in, it shows 5.

Fairly certain you can't do that.

 

You can put something in A1 and B1 will automatically divide it by 2, but you can't type something in A1 and have A1 show that number divided by 2.

[ewerk 31255]

Just do what Fish says then hide column A.

[wykikitoon 20956]

Sound

 

I have done it by placing a value in and then copying it, selecting all the cells and using paste special. Works fine.

[The Fish 10978]

That works, but in future if you drop the figures into column A then type =A1/2 in B1 and double click the bottom right of cell B1 it'll automatically fill that column with the numbers from column A divided by 2. That way if you need to do it again at some point, you can paste the new figures into column A and column B will update with the new figures divided by 2.

[Leon 13]

How many cells do you need this to apply to?

[wykikitoon 20956]

Righto

Another Excel one for you.  I organise the clubs annual league and at the end of the season we hand out T-Shirts.  T-Shirts are split into categories.  Is there an automated way of calculating the total of each category I need?  I.e 5 Small Gold, 2 Medium Bronze etc?

 

Edited November 16, 2016 by wykikitoon

[ewerk 31255]

You have eleven people in your league. Work it out in your head you lazy bastard,

[wykikitoon 20956]

You have eleven people in your league. Work it out in your head you lazy bastard,

 

 

We have a lot more, this is just a quick snap shot for ease.  In the blokes league we have 168!

[Gemmill 46273]

SUMIF motherfucker. Easiest for you to Google it and you'll get an example that will sort you out.

[The Fish 10978]

I'd not set it out like that mate. 

 

Easier to group three blocks and total them

[Gemmill 46273]

Also to make this easier, instead of having a column for size and a column for colour category and recording two entries for each shirt, have a column called Small Gold, Small Silver, etc etc. That way you only have to record the data once and it'll be much easier to get to what you're trying to do.

[Gemmill 46273]

What Fish said but mine is even better.

 

Fish, I hope you haven't used merge cells there. People who use merge cells are animals.

[wykikitoon 20956]

Class thanks

[The Fish 10978]

What Fish said but mine is even better.

 

Fish, I hope you haven't used merge cells there. People who use merge cells are animals.

Only in the interests of clarity.

 

Yours works too, by the way, well done.

[Gemmill 46273]

Centre across selection is the choice of the professional.

[The Fish 10978]

Centre across selection is the choice of the professional.

[Tooj 17]

Only plebs use Excel. It's all about Tableau these days babeh.

[Rayvin 5365]

I have one I could do with some help on please.

 

I'm developing a pricing matrix for a new line of products we're looking at putting out, and am trying to give a projected average sale price based on historical evidence from a similar product range. For each product I have 6 different subdivisions, and my historical analysis looks at what proportion of the overall number of products fall into each subdivision. So for instance, Subdivision A represents 2% of the overall number of products released in the past year, SubDiv B represents 15% and so on. Each subdivision is priced differently.

 

So the %ages all come up to 100% obviously. I want to provide an average sale price across all subdivisions, with the average taking into account the proportional split of the subdivisions. Can anyone advise how this can be done simply? My brain keeps wanting to just multiply the percentages into the prices, add them all up and then average in the normal way, but that obviously won't work

[The Fish 10978]

I'm not sure I follow, but I took it as

 

SubA = 2% of total sales

Sub A price = £100

Sub B = 15%

Sub Price = £5

...

Sub E = 25%

Sub E price = £0.5

 

 

So the average price will be be skewed heavily towards the Sub E because there are far more of them sold.

 

Wouldn't it be better to focus on volume sold of the individual Sub, rather than dealing with %s? 

 

so say Sub A sold 1 unit at £100, but Sub E sold 19 units at £0.5 the average price of an individual unit is £5.475 ( ((1xA)+(19*E))/20=5.475 )

And obviously if you do that for each 6 products and average the results, you should get the average unit price across the product line, right?

 

I'm not sure, I'd want to see the sheet to be honest.

Is that what you're after?

Edited November 29, 2016 by The Fish

[Rayvin 5365]

I suppose you're right about the volumes, it's just that it's not really looking at the number of copies sold, it's the number of new products released in each Sub. That said the logic would still stand anyway. I'll run it all in tomorrow and see where I get, thanks for that.

 

I've spent the whole day working on pulling all of this together so it's quite possible that I've overlooked something more simple than where I ended up.

 

Cheers again.

[The Fish 10978]

You're not after the price of the average product are you? You're after the average price across all products, right?

[Rayvin 5365]

Yep I was - I did what you said in terms of taking the total number of products rather than worrying about the %ages in the end, so thanks for that.

[The Fish 10978]

Yep I was - I did what you said in terms of taking the total number of products rather than worrying about the %ages in the end, so thanks for that.

No worries.

 

I wonder if there's an easy way for @@Ant to allow the uploading of tables into the reply box, that'd make stuff like this (and HF's tables) much easier.

[The Fish 10978]

Ah the life of a man living in fear of the fickle whims of his tyrannical overlord.