Jump to content

Excel People

Recommended Posts

  • Replies 96
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

This fucking thread, man!    

Use index match match. Its basically vlookup's big brother.    Have a look here, it'll walk you through how the index and match functions work separately, and then how they can be combined t

I am sure that there is a good way of doing this, but I don't know it. So my cheap workaround way would be the following:   Put =A1-1 in the first cell. Copy it down - you'll end up with the

Posted Images

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites
  • 3 months later...


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 by wykikitoon
Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites

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 :lol:

Link to post
Share on other sites

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 by The Fish
Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...