Interesting Lookup Problem

A

axed

New Member
Joined
Aug 29, 2010
Messages
15
Hi,

I have a two columns
1 0.5
1 0.4
1 0.2
2 0.3
2 0.2
3 0.25
3 0.2
3 0.4
3 0.2
4 0.8

What I need to do is multiply all the elements in the second column with the same first column field
i.e.
1 (0.5*0.4*0.2)
2 (0.3*0.2)
3 (...

I was wondering can I get away without writing a VBA script.

Thanks in advance.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Assuming that A1:B10 contains the data, let D1:D3 contain 1, 2, and 3, then try...

E1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=PRODUCT(IF($A$1:$A$10=D1,$B$1:$B$10))
 
Upvote 0
Thanks very much. One more thing, the first column that contains 1,2,3... is actually not sequential numbers. How should I create a unique list of these numbers in another column.
 
Upvote 0
A quick and simple way would be to use the 'Advanced Filter', and select 'Unique records only'.
 
Upvote 0
Below. It doesn't seem to work for me.
Code: 
200710	1.002448385	200710	5.240944638
200710	1.000960353	200711	0
200710	1.00560925	200712	0
200710	0.999847093	200801	0
200710	1.014638061	200802	0
200710	0.997037699	200803	0
200711	0.987729368	200804	0
200711	1.009796233	200805	0
200711	1.003206494	200806	0
200711	0.995991673	200807	0
200711	0.997439245	200808	0
200711	0.990844307	200809	0
200711	1.002990229	200810	0
200711	1.000625582	200811	0
200711	1.023459076	200812	0
200711	0.995591139	200901	0
...
Here the last column is
=PRODUCT(IF($A$1:$A$647=C1,$B$1:$B$647))
=PRODUCT(IF($A$1:$A$647=C2,$B$1:$B$647))
=PRODUCT(IF($A$1:$A$647=C3,$B$1:$B$647))
..
 
Upvote 0
Did you confirm the formula with CONTROL+SHIFT+ENTER? If done correctly, Excel will automatically place curly braces {...} around the formula.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
How to multiply two arrays together
Replies
1
Views
137
FlashDota
F
H
  • Solved
Index and match formula that ignores dynamic value
2
Replies
14
Views
422
FormR
FormR
D
  • Solved
How does one access a chart Series address in VBA?
Replies
8
Views
586
DrJBN
D
J
  • Solved
Lookup on every other column
2
Replies
18
Views
462
Fluff
Fluff
S
  • Question
Dynamic Array needed
2
Replies
10
Views
366
felixstraube
F

Forum statistics

Threads
1,215,338
Messages
6,124,358
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top