It seems that you're using an outdated browser. Some things may not work as they should (or don't work at all).
We suggest you upgrade newer and better browser like: Chrome, Firefox, Internet Explorer or Opera

×
Community
General discussion (archive)interesting maths problem in Excel(35 posts)solved(35 posts)solved
(35 posts)solved
Pages:
1
2
3
This is my favourite topic
Posted November 27, 2014
Got an interesting maths problem i just cannot wrap my head around in Excel...

What i have are five separate purchases each with a different quantity and price.

In column A1 to A5 will be the quantity of each purchase.
In column B1 to B5 will be the price for each purchase.

The price is the same for all the quantity in the same purchase but different for each separate purchase

I want to know the average price per piece? Answer shown in Cell B6

Totally stumped finding what single formula in Excel makes that work?
This question / problem has been solved by AnonAnomimage
Posted November 27, 2014
avatar
mystikmind2000: Got an interesting maths problem i just cannot wrap my head around in Excel...

What i have are five separate purchases each with a different quantity and price.

In column A1 to A5 will be the quantity of each purchase.
In column B1 to B5 will be the price for each purchase.

The price is the same for all the quantity in the same purchase but different for each separate purchase

I want to know the average price per piece? Answer shown in Cell B6

Totally stumped finding what single formula in Excel makes that work?
What do you mean by 'average price per piece'?
What do you consider a 'piece'?

Do you want an average of all purchases, with quantity factored in, or just the prices themselves, or something else altogether?
Posted November 27, 2014
I assume it means weighted average price, weighted by the different quantities.
So "=sumproduct(A1:A5,B1:B5)/sum(A1:A5)"?
Posted November 27, 2014
=(A1*B1+A2*B2+A3*B3+A4*B4+A5*B5)/(A1+A2+A3+A4+A5) if I understood correctly.

Ninja'd, but leaving it as an alternative.
Post edited November 27, 2014 by madth3
Posted November 27, 2014
avatar
AnonAnom: "=sumproduct(A1:A5,B1:B5)/sum(A1:A5)"?
^ this
Posted November 27, 2014
avatar
AnonAnom: I assume it means weighted average price, weighted by the different quantities.
So "=sumproduct(A1:A5,B1:B5)/sum(A1:A5)"?
Yes. If that's what the OP meant, then this is certainly the way to go! :)
Posted November 27, 2014
avatar
AnonAnom: I assume it means weighted average price, weighted by the different quantities.
So "=sumproduct(A1:A5,B1:B5)/sum(A1:A5)"?
oh, looks good, ,,, i don't know if it is correct but the figure looks about rite, thanks

Dare i ask how you figured that out? It is not like Excel is going to tell you in the 'help' files hahahaha, (sorry, whenever i use the term 'help files' in any sentence about a Microsoft product i cannot help laughing).
Posted November 27, 2014
avatar
AnonAnom: I assume it means weighted average price, weighted by the different quantities.
So "=sumproduct(A1:A5,B1:B5)/sum(A1:A5)"?
avatar
mystikmind2000: oh, looks good, ,,, i don't know if it is correct but the figure looks about rite, thanks

Dare i ask how you figured that out? It is not like Excel is going to tell you in the 'help' files hahahaha, (sorry, whenever i use the term 'help files' in any sentence about a Microsoft product i cannot help laughing).
I just know this stuff from experience.
Posted November 27, 2014
avatar
mystikmind2000: oh, looks good, ,,, i don't know if it is correct but the figure looks about rite, thanks

Dare i ask how you figured that out? It is not like Excel is going to tell you in the 'help' files hahahaha, (sorry, whenever i use the term 'help files' in any sentence about a Microsoft product i cannot help laughing).
avatar
AnonAnom: I just know this stuff from experience.
Thats the best way! And thanks again for your help, and others too
Posted November 27, 2014
avatar
AnonAnom: I assume it means weighted average price, weighted by the different quantities.
So "=sumproduct(A1:A5,B1:B5)/sum(A1:A5)"?
avatar
mystikmind2000: oh, looks good, ,,, i don't know if it is correct but the figure looks about rite, thanks

Dare i ask how you figured that out? It is not like Excel is going to tell you in the 'help' files hahahaha, (sorry, whenever i use the term 'help files' in any sentence about a Microsoft product i cannot help laughing).
I'm surprised that anybody still uses Excel. It's buggy, the interface is the worst one on the market and the programmers don't even bother to make sure that things like linear regressions provide the correct results.
Posted November 27, 2014
avatar
AnonAnom: I assume it means weighted average price, weighted by the different quantities.
So "=sumproduct(A1:A5,B1:B5)/sum(A1:A5)"?
avatar
mystikmind2000: oh, looks good, ,,, i don't know if it is correct but the figure looks about rite, thanks

Dare i ask how you figured that out? It is not like Excel is going to tell you in the 'help' files hahahaha, (sorry, whenever i use the term 'help files' in any sentence about a Microsoft product i cannot help laughing).
Maybe he asked the dreaded Office Paperclip! =P
Posted November 27, 2014
Rats, was hoping to use "goalseek".

I love goalseek.
Posted November 27, 2014
avatar
hedwards: I'm surprised that anybody still uses Excel. It's buggy, the interface is the worst one on the market and the programmers don't even bother to make sure that things like linear regressions provide the correct results.
Because it's a fantastic tool?
Posted November 27, 2014
avatar
hedwards: I'm surprised that anybody still uses Excel. It's buggy, the interface is the worst one on the market and the programmers don't even bother to make sure that things like linear regressions provide the correct results.
avatar
Wishbone: Because it's a fantastic tool?
Not really, it used to be a fantastic tool, but people just use it out of inertia at this point. There's a few people out there that use the macro capabilities, but for what most people do with it, it's just not a very good program.
Posted November 27, 2014
avatar
hedwards: [...] the programmers don't even bother to make sure that things like linear regressions provide the correct results.
Can you provide an example of Excel doing an incorrect linear regression?
Pages:
1
2
3
This is my favourite topic
Community
General discussion (archive)interesting maths problem in Excel(35 posts)solved(35 posts)solved
(35 posts)solved