Sumproduct Formula

[SeaTiger]

Hello friends,

I have a worksheet that has my stock transactions. In column D, I have the # of shares bought with the cells formatted as a Number, and, in column F with the cells formatted as Accounting, where I have the total purchase cost for the date of the purchase for those shares.

I want to divide the total purchase cost for, let's say stock A, by the total # of shares bought so I can determine the net cost of each share. For this particular Stock A, I made 2 purchases on separate dates so I created the following formula to determine the net cost of each share by dividing the sum of the total purchase $ by the sun of the total # of shares bought.

=SUMPRODUCT(SUM(F11:F12))/(SUM(D11:D12))*-1 This worked & produced a net cost of $94.23.

Then, I wanted to add the following text to the beginning of the formula, "Net Cost $," so I created this formula:

="Net Cost "&SUMPRODUCT(SUM(F11:F12))/(SUM(D11:D12))*-1 However, this produced, Net Cost $94.225

I formatted the cell to 2 decimals, so I can't figured out how to get it to round up to a whole #, $94.23. What can I do?

Also, is there any other formula suggestions beside SUMPRODUCT? Thank you for help!

 

[jasonb75]

When you add text to the cell, formatting no longer applies as the content of the cell is not numeric.

="Net Cost "&ROUNDUP(SUMPRODUCT(SUM(F11:F12))/(SUM(D11:D12))*-1,2)

Also, is there any other formula suggestions beside SUMPRODUCT?

I don't see that SUMPRODUCT serves any purpose here, this looks like it should do the same with less clutter

="Net Cost "&-ROUNDUP(SUM(F11:F12)/SUM(D11:D12),2)

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Sumproduct Formula
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[SeaTiger]

When you add text to the cell, formatting no longer applies as the content of the cell is not numeric.

="Net Cost "&ROUNDUP(SUMPRODUCT(SUM(F11:F12))/(SUM(D11:D12))*-1,2)

I don't see that SUMPRODUCT serves any purpose here, this looks like it should do the same with less clutter

="Net Cost "&-ROUNDUP(SUM(F11:F12)/SUM(D11:D12),2)

Both formulas worked after modifying the cells & removing the - before ROUNDOUP from the 2nd formula. THANK YOU!

="Net Cost $"&ROUNDUP(SUMPRODUCT(SUM(F10:F11))/(SUM(D10:D11))*-1,2)
="Net Cost $"&ROUNDUP(SUM(F10:F11)/SUM(D10:D11)*-1,2)

 

[SeaTiger]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Sumproduct Formula
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Sorry, here is the link to the site url that I cross posted, Sumproduct Formula

 

[jasonb75]

removing the - before ROUNDOUP from the 2nd formula.

Unless you erroneously put the *-1 that I removed back into the formula, it works correctly with the - before ROUNDUP.