Somproduct met dynamische input

[Erik111]

Hallo,

Ik heb een vraag over een item waar ik echt niet uit kom, kan het ook nergens online (of op dit forum) vinden. Ik hoop dat iemand van u me kan helpen. Het werkelijk probleem zit iets ingewikkelder in elkaar, maar ik wil hier graag een voorbeeld geven.

Stel in heb twee kolommen met data, waarbij de cellen van de eerste kolom een formule bevatten met een input uit een losstaande cel bv:

Cell A1 = 1
Cell A2 = 10000
Cell A3 = 5000

Ik gebruik nu een formule

Kolom1 Kolom2
=A2/$A$1 1
=A3/$A$1 3

Laat stellen dat het bereik van kolom1 = B2:B3 en van kolom2 = C2:C3

Als ik nu het somproduct neem: somproduct(B2:B3;C2:C3) dan volgt het resultaat: 25000. Als ik nu Cell A1 verander in 2, dan zou het sumproduct 12500 zijn.

Wat ik nu nodig heb is een manier om een een array te verkrijgen van het somproduct(B2:B3;C2:C3) waarbij ik cell A1 steeds verander. Dus een array (tweede kolom) die er zo uit zou zien:

"Cell A1" somproduct(B2:B3;C2:C3)
1 25000
2 12500
3 8333.33
4 etc.

Ik hoop dat het een beetje duidelijk is. Het komt erop neer dat ik dus het somproduct van 2 kolommen wil bepalen, maar dat steeds opnieuw voor veranderende waarde van Cell A1 en die waarden in een nieuwe kolom zetten. Bedankt alvast voor de hulp.

Erik

 

[HSV]

Tja, een mooi lang verhaal Erik, maar aan een voorbeeldje in .xls vorm zegt meer.

 

[Erik111]

Je hebt gelijk, sorry. Hierbij een voorbeeld toegevoegd. Het gaat nu om somproduct(A6:A7;B6:B7) gegeven B1.

Ik wil nu een formule in E2 die het somproduct(A6:A7;B6:B7) geeft maar dan met D2 als input ipv B1 (zodat ik in E3 dit somproduct krijg met D3 als input ipv B1 etc).

Bekijk bijlage voorbeeld_dyn_somproduct.xlsx

Dank alvast.

 

[Erik111]

hier ook nog een keer in excel2003 format
Bekijk bijlage voorbeeld_dyn_somproduct2003.xls

 

[Wigi]

Zoiets als in bijlage misschien?

 

[Erik111]

Bedankt voor je bericht. Het is een leuke oplossing, maar helaas is het door het versimpelde voorbeeld niet echt van toepassing op mijn werkelijke worksheet, welke eens stuk groter is en iets gecompliceerder in elkaar zit. Ik wil het eigenlijk zonder dat ik een kolom F, G, H en I bij heb. Ik kan dus geen kolommen toevoegen voor deze tussenstap, alles moet op één regel in functie/formule vorm in E2, waarna ik deze naar beneden kan slepen en dit somproduct berekend voor alle entries in kolom D. Ik hoop dat zoiets kan in een enkele formule.

Erik

 

[Wigi]

In cel E2:

=SOMPRODUCT($B$2:$B$3/$D2;$B$6:$B$7)

 

[Erik111]

Wederom bedankt, maar sorry, weer door dit specifieke voorbeeld kan het niet. Ik ga nu een stuk uit mijn originele sheet knippen en plakken om het werkelijke probleem te laten zien. Ik zet hem over een paar minuten online.

 

[Wigi]

weer door dit specifieke voorbeeld kan het niet.

Je kan misschien eerst zelf eens proberen door met de aangereikte formules en gedachtegang aan de slag te gaan. Het is heus geen hogere wiskunde ;-) Blijven er dan nog problemen over kan je uiteraard opnieuw een beroep doen op de helpers.

Ik ga geen derde keer "gokken" naar een oplossing omdat het voorbeeldje niet representatief is voor de echte file.

 

[Erik111]

Ja, je hebt een goed punt. Ik kan je vertellen, ik heb al twee dagen zitten denken en heb al zoveel geprobeerd. Hierbij toegevoegd het werkelijke en dus representatieve probleem.

Het is een sheet om (index) opties te prijzen met de black and scholes formule (in dit voorbeeld zijn alleen de prijzen van belang). De prijzen van de call opties worden berekend in kolom E (32 t/m 46) en van de put opties kolom I (32 t/m 46). De aantallen opties in positie staan in kolom F en kolom H. De fixed inputs voor de black and scholes formule staan in kolom C (2 t/m 12), tevens worden ook als variabele inputs per strike de waarden uit kolom A (32 t/m 46) en G (32 t/m 46) gebruikt.

In entry K24 bepaal ik de waarde van mijn positie met het somproduct (aantallen opties * Price). Dit doe ik voor fixed input C2 = 360

Nu wil ik dus voor elke strike van 300 tot 400 (zie kolom R) weten wat de waarde van mijn optie positie is. Ik wil dus feitelijk weten wat SOMPRODUCT(F32:F46;E32:E46)+SOMPRODUCT(H32:H46*I32:I46))*100 en die waardes geven in kolom T.

in T2 komt: 339982.78
in T3 komt: 334082.78

Deze twee waarden verkreeg ik nu door in C2 de waardes 300 en 301 te stoppen en te kijken wat de Value is. Nu wil ik dit dus automatisch voor al de 100 strikes uitrekenen. Ik hoop dat dit wat duidelijker is. Bedankt voor de moeite. (zoals je ziet is die ene eerste oplossing van je mogelijk maar dat betekent dat ik alle aantallen in kolommen moet wegschrijven, maar in de werkelijke situatie heb je honderden strikes met aantallen, dus dan zou je honderden kolommen moeten gebruiken. Het werkt, maar is practisch zeer onhandig). Ik waardeer het als je nog een keer een kijkje kan nemen.

Erik

Bekijk bijlage Option_Value_Sheet.xlsx

 

[WHER]

Kijk eens of dit bestand doet wat je in gedachten had. Ik ben niet vertrouwd met de financiële terminologie, dus deze aanpak kan er ook helemaal naast zitten.

 

[Erik111]

Geniaal, dit werkt als een trein. Super bedankt. Alleen één dingetje nu nog, ik ben zelf niet heel erg thuis in macro's; hoe krijg ik deze macro in mijn originele sheet gekopieerd??

Erik

 

[WHER]

Rechtsklik op de sheet-tab van Blad1 (of Alt-F11), dan zou je de macro "Sub val()" moeten zien. Selecteer en kopiëer de lijnen code tot End Sub, rechtsklik op de overeenkomstige sheet-tab in je originele bestand en plak de code, normaal gezien vlak onder "Option Explicit".
Vervolgens kun je bijvoorbeeld een rechthoek tekenen en de macro daar aan toewijzen door rechts te klikken op de getekende rechthoek en via "Assign macro" de macro te selecteren.

 

[Erik111]

Heb de code in mijn eigen sheet gezet. Macro gelinkt aan een button etc. Nu runt ie wel, maar de waardes worden niet in mijn kolom gezet :-s in dit geval in kolom V. (blad toegevoegd)
Ik hoop dat ik niet al te vervelend ben, maar weet jij hoe ik dat op moet lossen?

Bekijk bijlage simulatie.xlsm

 

[WHER]

cl.Value = Range("N23").Value

moet zijn

cl.Value = Range("M23").Value

 

[Erik111]

Oh man, wat stom van me, ik had die rijen samengevoegd natuurlijk.

Ik waardeer heel erg dat je me hebt geholpen. Super bedankt.

Erik