Small & multiply if function combo

cgsierra

Board Regular
Joined
Mar 21, 2011
Messages
132
I am trying to make a formula which gives me the smallest operating PROFIT of a subtotaled data set that only contains Revenue (column B) and Margin (column C). In other words:
A1=x, B1=$10, C1=5%
A2=x, B2=$20, C2=3%
A3="subtotal x", B3=$30, C3=3.7%
A4=y, B4=$40, C4=8%
A5=y, B5=$20, C5=7%
A6=y, B6=$50, C6=15%
A7="subtotal y", B7=$110, C7=11%
A8=z, B8=$100, C8=7%
A9=z, B9=$20, C9=10%
A10="subtotal z", B10=$120, C10=7.5%
The formula should return: $1.11 (which is 30*3.7% which is the smallest of the 3 operating profits of the subtotals)
I am using: {=SMALL((MMULT(GAM!$S$9:$S$34,GAM!$T$9:$T$34))*(GAM!$C$9:$C$34="Sub-Total"),1)} but I keep getting a #value! message.
Your help is greatly appreciated!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
not too sure on the MMULT function so I looked it up

The number of columns in array1 must be the same as the number of rows in array2, and both arrays must contain only numbers.
Array1 and array2 can be given as cell ranges, array constants, or references.
MMULT returns the #VALUE! error when:
  • Any cells are empty or contain text.
  • The number of columns in array1 is different from the number of rows in array2.
Looking at this you have a lot wrong with your formula.
you have all columns and you appear to be looking up text


I think you need another fourmula altogether
 

cgsierra

Board Regular
Joined
Mar 21, 2011
Messages
132
Thank you. In terms of array 1 (column B) and array (column C), they are both only numbers and contain the same array size. However, there are blank spaces, which I need for presentation purposes. I would enter dummy figures and color white in the empty spaces but the data set changes and it is a big database. Can you recommend another formula?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,208
Untested, but this CSE formula might do it

=SMALL(IF(A1:A10="subtotal", B1:B10*C1:C10, 9E+99), 1)

This formula should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)
 

cgsierra

Board Regular
Joined
Mar 21, 2011
Messages
132
I am trying to make a formula which gives me the smallest operating PROFIT of a subtotaled data set that only contains Revenue (column B) and Margin (column C). In other words:
A1=x, B1=$10, C1=5%
A2=x, B2=$20, C2=3%
A3="subtotal", B3=$30, C3=3.7%
A4=y, B4=$40, C4=8%
A5=y, B5=$20, C5=7%
A6=y, B6=$50, C6=15%
A7="subtotal", B7=$110, C7=11%
A8=z, B8=$100, C8=7%
A9=z, B9=$20, C9=10%
A10="subtotal", B10=$120, C10=7.5%
The formula should return: $1.11 (which is 30*3.7% which is the smallest of the 3 operating profits of the subtotals)
I am using: {=SMALL((MMULT(GAM!$S$9:$S$34,GAM!$T$9:$T$34))*(GAM!$C$9:$C$34="Sub-Total"),1)} but I keep getting a #value! message.
Your help is greatly appreciated!
The applicable formula with the data set above is actually:
{=SMALL((MMULT(GAM!$B$1:$B$10,GAM!$C$1:$C$10))*(GAM!$A$1:$C$10="subtotal"),1)}, instead of the original I posted, sorry if this caused any confussion.
 

cgsierra

Board Regular
Joined
Mar 21, 2011
Messages
132
Untested, but this CSE formula might do it

=SMALL(IF(A1:A10="subtotal", B1:B10*C1:C10, 9E+99), 1)

This formula should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

This is EXACTLY what I was looking for! Thank you!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,899
Messages
5,834,299
Members
430,275
Latest member
Halis

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
Top