Formula Help Needed (Excel 2007)

[clock4869]

All,

I apologize in advance for creating a new post, but i realized i did not state my problem fully from my previous post after going thru my dataset. the below spreadsheet represents my data values. Colums A-D and G-H are given. RichardSchollar helped me with E by creating a formula that takes the type of car and compares it to the available budget and then substracts the sum of the costs in order of need date , but i did not fully explain the problem to him.
An additional wrinkle to this problem is that even though i do need to pay attention to need date i have to give priority to some particular drivers first. So if you take Honda in the below spreadsheet you will notice that both Bill and Otis gets it. It would appear initially that Otis gets the first, then Bill, then Otis again, but unfortunately Otis would get preference always in this case. So the Honda budget would need to fill Otis's needs first and then go back and fill Bill's needs with the remaining budget or show a shortfall if there isn't enough money.

I've tried to develop some If statements, but I believe i am way off track. Is it possible to build a formula for this scenario? I appreciate all the help and advice. I've been struggling with this for quite some time now instead of asking again

Here is the modfied spreadsheet:

Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: MS Sans Serif,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 73px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 70px"><COL style="WIDTH: 16px"><COL style="WIDTH: 94px"><COL style="WIDTH: 68px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><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></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">Need Date</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">Type</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">Driver</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">Cost</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">Remaining Budget</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center" colSpan=2>Budget</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">JAN</TD><TD style="TEXT-ALIGN: center">Honda</TD><TD style="TEXT-ALIGN: center">Otis</TD><TD style="TEXT-ALIGN: center">$10,000</TD><TD style="TEXT-ALIGN: center">$25,000</TD><TD> </TD><TD style="TEXT-ALIGN: center">Honda</TD><TD style="TEXT-ALIGN: center">$35,000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">JAN</TD><TD style="TEXT-ALIGN: center">Toyota</TD><TD style="TEXT-ALIGN: center">Bill</TD><TD style="TEXT-ALIGN: center">$15,000</TD><TD style="TEXT-ALIGN: center">$10,000</TD><TD> </TD><TD style="TEXT-ALIGN: center">Toyota</TD><TD style="TEXT-ALIGN: center">$25,000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">FEB</TD><TD style="TEXT-ALIGN: center">Honda</TD><TD style="TEXT-ALIGN: center">Bill</TD><TD style="TEXT-ALIGN: center">$12,000</TD><TD style="TEXT-ALIGN: center">$13,000</TD><TD> </TD><TD style="TEXT-ALIGN: center">GM</TD><TD style="TEXT-ALIGN: center">$16,000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">FEB</TD><TD style="TEXT-ALIGN: center">Ford</TD><TD style="TEXT-ALIGN: center">Otis</TD><TD style="TEXT-ALIGN: center">$16,000</TD><TD style="TEXT-ALIGN: center">$14,000</TD><TD> </TD><TD style="TEXT-ALIGN: center">Ford</TD><TD style="TEXT-ALIGN: center">$30,000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">MAR</TD><TD style="TEXT-ALIGN: center">Toyota</TD><TD style="TEXT-ALIGN: center">Paul</TD><TD style="TEXT-ALIGN: center">$15,000</TD><TD style="TEXT-ALIGN: center">-$5,000</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">MAR</TD><TD style="TEXT-ALIGN: center">Honda</TD><TD style="TEXT-ALIGN: center">Otis</TD><TD style="TEXT-ALIGN: center">$13,000</TD><TD style="TEXT-ALIGN: center">$0</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">MAR</TD><TD style="TEXT-ALIGN: center">GM</TD><TD style="TEXT-ALIGN: center">Bill</TD><TD style="TEXT-ALIGN: center">$14,500</TD><TD style="TEXT-ALIGN: center">$1,500</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">APR</TD><TD style="TEXT-ALIGN: center">Ford</TD><TD style="TEXT-ALIGN: center">Paul</TD><TD style="TEXT-ALIGN: center">$16,000</TD><TD style="TEXT-ALIGN: center">-$2,000</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=VLOOKUP($B2,$G$2:$H$5,2,0)-SUMIF($B$2:$B2,$B2,$D$2:$D2)</TD></TR><TR><TD>E3</TD><TD>=VLOOKUP($B3,$G$2:$H$5,2,0)-SUMIF($B$2:$B3,$B3,$D$2:$D3)</TD></TR><TR><TD>E4</TD><TD>=VLOOKUP($B4,$G$2:$H$5,2,0)-SUMIF($B$2:$B4,$B4,$D$2:$D4)</TD></TR><TR><TD>E5</TD><TD>=VLOOKUP($B5,$G$2:$H$5,2,0)-SUMIF($B$2:$B5,$B5,$D$2:$D5)</TD></TR><TR><TD>E6</TD><TD>=VLOOKUP($B6,$G$2:$H$5,2,0)-SUMIF($B$2:$B6,$B6,$D$2:$D6)</TD></TR><TR><TD>E7</TD><TD>=VLOOKUP($B7,$G$2:$H$5,2,0)-SUMIF($B$2:$B7,$B7,$D$2:$D7)</TD></TR><TR><TD>E8</TD><TD>=VLOOKUP($B8,$G$2:$H$5,2,0)-SUMIF($B$2:$B8,$B8,$D$2:$D8)</TD></TR><TR><TD>E9</TD><TD>=VLOOKUP($B9,$G$2:$H$5,2,0)-SUMIF($B$2:$B9,$B9,$D$2:$D9)</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



Many thanks,

 

[theozz]

row6 and row 9 is to be 0 ???

 

[clock4869]

No i apologize if i was unclear. Row 6&9 are supposed to be negaive values like they currently show. Meaning additional $$ must be sought.

Row 7 happened to be $0 because the budget was just enough to cover the third car in the example spreadsheet.

However, it's very likely that there will be cases in the future where there won't be enough money available to cover all the cars in a scenario. So if there are limited Honda $$ to spend and a high amount of $$ required then those dollars must first be spread over the drivers that need to be funded first by need date.

So in the above example Otis would be funded first by order of need date for his Hondas and then Bill would be funded. If there is a shortfall and there isn't enough $$ in the Honda budget for Bill then i'd like to show the value of the shortfall so i can declare a need for additional $XXX for Bill.

Hopefully this is clearer?

 

[clock4869]

any ideas at all on this one? I appreicate everyone's time an patience with me on this question