Is that possible in Excel?!

G

gledister

Board Regular
Joined
Mar 21, 2011
Messages
173
Imagine we have this situation below:
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">=A1-D1</TD><TD style="TEXT-ALIGN: right">=B1-E1</TD><TD style="TEXT-ALIGN: right">=C1-F1</TD><TD style="TEXT-ALIGN: right">=SUMIF(G1:I1;">0")</TD><TD style="TEXT-ALIGN: right">=SUMIF(G1:I1;"<0")</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">=A2-D1</TD><TD style="TEXT-ALIGN: right">=B2-E1</TD><TD style="TEXT-ALIGN: right">=C2-F1</TD><TD style="TEXT-ALIGN: right">=SUMIF(G2:I2;">0")</TD><TD style="TEXT-ALIGN: right">=SUMIF(G2:I2;"<0")</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">=A3-D1</TD><TD style="TEXT-ALIGN: right">=B3-E1</TD><TD style="TEXT-ALIGN: right">=C3-F1</TD><TD style="TEXT-ALIGN: right">=SUMIF(G3:I3;">0")</TD><TD style="TEXT-ALIGN: right">=SUMIF(G3:I3;"<0")</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G1</TD><TD>=A1-D1</TD></TR><TR><TD>H1</TD><TD>=B1-E1</TD></TR><TR><TD>I1</TD><TD>=C1-F1</TD></TR><TR><TD>J1</TD><TD>=SUMIF(G1:I1;">0")</TD></TR><TR><TD>K1</TD><TD>=SUMIF(G1:I1;"<0")</TD></TR><TR><TD>G2</TD><TD>=A2-D1</TD></TR><TR><TD>H2</TD><TD>=B2-E1</TD></TR><TR><TD>I2</TD><TD>=C2-F1</TD></TR><TR><TD>J2</TD><TD>=SUMIF(G2:I2;">0")</TD></TR><TR><TD>K2</TD><TD>=SUMIF(G2:I2;"<0")</TD></TR><TR><TD>G3</TD><TD>=A3-D1</TD></TR><TR><TD>H3</TD><TD>=B3-E1</TD></TR><TR><TD>I3</TD><TD>=C3-F1</TD></TR><TR><TD>J3</TD><TD>=SUMIF(G3:I3;">0")</TD></TR><TR><TD>K3</TD><TD>=SUMIF(G3:I3;"<0")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

vectors A1 to C1, A2 to C2 and A3 to C3 can be multiplied by every full positive number(0,1,2,3...etc)....for example A1:C1 with 1, A2 to C2 with 0, A3 to C3 with 2...in that case we will have:


Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">=A1-D1</TD><TD style="TEXT-ALIGN: right">=B1-E1</TD><TD style="TEXT-ALIGN: right">=C1-F1</TD><TD style="TEXT-ALIGN: right">=SUMIF(G1:I1;">0")</TD><TD style="TEXT-ALIGN: right">=SUMIF(G1:I1;"<0")</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">=A2-D1</TD><TD style="TEXT-ALIGN: right">=B2-E1</TD><TD style="TEXT-ALIGN: right">=C2-F1</TD><TD style="TEXT-ALIGN: right">=SUMIF(G2:I2;">0")</TD><TD style="TEXT-ALIGN: right">=SUMIF(G2:I2;"<0")</TD><TD> </TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">=A3-D1</TD><TD style="TEXT-ALIGN: right">=B3-E1</TD><TD style="TEXT-ALIGN: right">=C3-F1</TD><TD style="TEXT-ALIGN: right">=SUMIF(G3:I3;">0")</TD><TD style="TEXT-ALIGN: right">=SUMIF(G3:I3;"<0")</TD><TD> </TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">=$A$1:$C$1*$M$1</TD><TD style="TEXT-ALIGN: right">=$A$1:$C$1*$M$1</TD><TD style="TEXT-ALIGN: right">=$A$1:$C$1*$M$1</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">=A5-D1</TD><TD style="TEXT-ALIGN: right">=B5-E1</TD><TD style="TEXT-ALIGN: right">=C5-F1</TD><TD style="TEXT-ALIGN: right">=SUMIF(G5:I5;">0")</TD><TD style="TEXT-ALIGN: right">=SUMIF(G5:I5;"<0")</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">=$A$2:$C$2*$M$2</TD><TD style="TEXT-ALIGN: right">=$A$2:$C$2*$M$2</TD><TD style="TEXT-ALIGN: right">=$A$2:$C$2*$M$2</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">=A6-D1</TD><TD style="TEXT-ALIGN: right">=B6-E1</TD><TD style="TEXT-ALIGN: right">=C6-F1</TD><TD style="TEXT-ALIGN: right">=SUMIF(G6:I6;">0")</TD><TD style="TEXT-ALIGN: right">=SUMIF(G6:I6;"<0")</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">=$A$3:$C$3*$M$3</TD><TD style="TEXT-ALIGN: right">=$A$3:$C$3*$M$3</TD><TD style="TEXT-ALIGN: right">=$A$3:$C$3*$M$3</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">=A7-D1</TD><TD style="TEXT-ALIGN: right">=B7-E1</TD><TD style="TEXT-ALIGN: right">=C7-F1</TD><TD style="TEXT-ALIGN: right">=SUMIF(G7:I7;">0")</TD><TD style="TEXT-ALIGN: right">=SUMIF(G7:I7;"<0")</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G1</TD><TD>=A1-D1</TD></TR><TR><TD>H1</TD><TD>=B1-E1</TD></TR><TR><TD>I1</TD><TD>=C1-F1</TD></TR><TR><TD>J1</TD><TD>=SUMIF(G1:I1;">0")</TD></TR><TR><TD>K1</TD><TD>=SUMIF(G1:I1;"<0")</TD></TR><TR><TD>G2</TD><TD>=A2-D1</TD></TR><TR><TD>H2</TD><TD>=B2-E1</TD></TR><TR><TD>I2</TD><TD>=C2-F1</TD></TR><TR><TD>J2</TD><TD>=SUMIF(G2:I2;">0")</TD></TR><TR><TD>K2</TD><TD>=SUMIF(G2:I2;"<0")</TD></TR><TR><TD>G3</TD><TD>=A3-D1</TD></TR><TR><TD>H3</TD><TD>=B3-E1</TD></TR><TR><TD>I3</TD><TD>=C3-F1</TD></TR><TR><TD>J3</TD><TD>=SUMIF(G3:I3;">0")</TD></TR><TR><TD>K3</TD><TD>=SUMIF(G3:I3;"<0")</TD></TR><TR><TD>A5</TD><TD>=$A$1:$C$1*$M$1</TD></TR><TR><TD>B5</TD><TD>=$A$1:$C$1*$M$1</TD></TR><TR><TD>C5</TD><TD>=$A$1:$C$1*$M$1</TD></TR><TR><TD>G5</TD><TD>=A5-D1</TD></TR><TR><TD>H5</TD><TD>=B5-E1</TD></TR><TR><TD>I5</TD><TD>=C5-F1</TD></TR><TR><TD>J5</TD><TD>=SUMIF(G5:I5;">0")</TD></TR><TR><TD>K5</TD><TD>=SUMIF(G5:I5;"<0")</TD></TR><TR><TD>A6</TD><TD>=$A$2:$C$2*$M$2</TD></TR><TR><TD>B6</TD><TD>=$A$2:$C$2*$M$2</TD></TR><TR><TD>C6</TD><TD>=$A$2:$C$2*$M$2</TD></TR><TR><TD>G6</TD><TD>=A6-D1</TD></TR><TR><TD>H6</TD><TD>=B6-E1</TD></TR><TR><TD>I6</TD><TD>=C6-F1</TD></TR><TR><TD>J6</TD><TD>=SUMIF(G6:I6;">0")</TD></TR><TR><TD>K6</TD><TD>=SUMIF(G6:I6;"<0")</TD></TR><TR><TD>A7</TD><TD>=$A$3:$C$3*$M$3</TD></TR><TR><TD>B7</TD><TD>=$A$3:$C$3*$M$3</TD></TR><TR><TD>C7</TD><TD>=$A$3:$C$3*$M$3</TD></TR><TR><TD>G7</TD><TD>=A7-D1</TD></TR><TR><TD>H7</TD><TD>=B7-E1</TD></TR><TR><TD>I7</TD><TD>=C7-F1</TD></TR><TR><TD>J7</TD><TD>=SUMIF(G7:I7;">0")</TD></TR><TR><TD>K7</TD><TD>=SUMIF(G7:I7;"<0")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


At the same time cells A1 to C1, A2 to C2 and A3 to C3 can be multiplied and summed one with each other, for example A1 to C1 can be multiplied by 1 and then can be summed with vector A2:C2....

I know this is getting to long but am at the end:

What we want to have is:

Which are the best alternatives to do with these vectors to achieve values the nearest possible to ZERO at J1:K3 ?!?!

Thanks in advance!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
If you really have ANY question to understand better the problem PLEASE feel free to make any question you would like to!

Thanks in advance!
 
Upvote 0
Explain what the purpose of this is. What are you trying to achieve ( and don't just say "to achieve values the nearest possible to ZERO at J1:K3" )?
 
Upvote 0
Imagine that vectors A1:B1:C1, A2:B2:C2, A3:B3:C3 are three different boxes of the same item in different sizes and in different quantities for each box which are given to us from the seller that we can buy in quantity we want....(A1,A2,A3) contains one size, example an S size...B1,B2;B3 an M size...and C1,C2,C3 an L size

When we buy we must buy the whole box...and Vector D1:F1 is what we really sold of them...for example we sold 4S, 3M, 3L...so our problem is to understand which boxes we must buy from the seller to optimize our costs....

If you have ANY other question PLEASE let me know!

Kind regards.
 
Upvote 0
And values in J1:J2:J3 are items which will remain in our store unsold and values K1:K2:K3 are unsatisfied customers....

Hope am clear enough in exposing my problem.

Please feel free to make any question!

Regards and thanks in advance!
 
Upvote 0
I am logging off now ... but I'm sure there are others that may look at this until I log on again tomorrow.
 
Upvote 0
Re: Is that possible in Excel?

Hello All,

Really a big question for me. I don't have any knowledge regarding this. I am also interested to this. Please tell me the answer.
 
Upvote 0
You could do this with Solver:

Code: 
      ----B----- -C- -D- -E- F ---G---
  2               S   M   L    Ord Qty
  3         Pkg1  1   2   1       2   
  4         Pkg2  2   3   1       0   
  5         Pkg3  3   1   2       0   
  6                                   
  7     Qty Sold  2   4   2    Excess 
  8   Qty Bought  2   4   2       0

The formula in C8 and across is =SUMPRODUCT(C3:C5, $G3:$G5)

In G8, =SUMPRODUCT(C8:E8-C7:E7)

Use Solver to minimize G8 by changing G3:G5 subject to the constraints that G3:G5 are integer, G3:G5 >=0, and C8:E8 >= C7:E7
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
R
  • Question Question
Find the value in a string using the corresponding phrase
Replies
1
Views
64
reznx
R
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
107
flashgordie
F
G
  • Question Question
Rearrange data into two columns
Replies
3
Views
73
Fluff
Fluff
R
  • Question Question
import specific columns from one workbook to another from matching header with vba
Replies
1
Views
136
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

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