Excel Formula

[jabbaman]

Hi all, I'm new to MrExcel
I'm trying to work out which input to use for this scenario.
In cell A2 i have 4 different win prices i can use
So what i want to be able to do is when i enter a price in A2 it will automatically put the outlay amount in cell B2 and in cell C2 when i enter 1st i want it to automatically enter the amount from cell D2 into E2 , obviously if the result in C2 is either 2nd,3rd or Unplaced it would automatically enter $0.00. I hope someone can help me with this, thanks.
A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1
[TABLE="width: 838"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Win Price[/TD]
[TD]Outlay[/TD]
[TD]Finished[/TD]
[TD]Win Amount[/TD]
[TD]Won/Lost[/TD]
[TD]Difference[/TD]
[TD]Total Outlay[/TD]
[TD]Profit/Loss[/TD]
[TD] Prices Min [/TD]
[TD] Prices Max [/TD]
[TD]Bet[/TD]
[TD]Position[/TD]
[/TR]
[TR]
[TD]$1.55[/TD]
[TD]$250.00[/TD]
[TD]1st[/TD]
[TD]$387.50[/TD]
[TD]$387.50[/TD]
[TD="align: right"]$137.50[/TD]
[TD="align: right"]$250.00[/TD]
[TD]$137.50[/TD]
[TD]$1.00[/TD]
[TD]$2.00[/TD]
[TD]$250[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]$1.80[/TD]
[TD]$250.00[/TD]
[TD]3rd[/TD]
[TD]$450.00[/TD]
[TD]$0.00[/TD]
[TD="align: right"]-$250.00[/TD]
[TD="align: right"]$500.00[/TD]
[TD]-$112.50[/TD]
[TD]$2.01[/TD]
[TD]$3.00[/TD]
[TD]$200[/TD]
[TD]2nd[/TD]
[/TR]
[TR]
[TD]$2.70[/TD]
[TD]$200.00[/TD]
[TD]1st[/TD]
[TD]$540.00[/TD]
[TD]$540.00[/TD]
[TD="align: right"]$340.00[/TD]
[TD="align: right"]$700.00[/TD]
[TD]$227.50[/TD]
[TD]$3.01[/TD]
[TD]$4.00[/TD]
[TD]$150[/TD]
[TD]3rd[/TD]
[/TR]
[TR]
[TD]$7.00[/TD]
[TD]$100.00[/TD]
[TD]Unplaced[/TD]
[TD]$700.00[/TD]
[TD]$0.00[/TD]
[TD="align: right"]-$100.00[/TD]
[TD="align: right"]$800.00[/TD]
[TD]$127.50[/TD]
[TD]$4.01[/TD]
[TD]$150.00[/TD]
[TD]$100[/TD]
[TD]Unplaced[/TD]
[/TR]
[TR]
[TD]$3.40[/TD]
[TD]$150.00[/TD]
[TD]1st[/TD]
[TD]$510.00[/TD]
[TD]$510.00[/TD]
[TD="align: right"]$360.00[/TD]
[TD="align: right"]$950.00[/TD]
[TD]$487.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$0.00[/TD]
[TD][/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$950.00[/TD]
[TD]$487.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$0.00[/TD]
[TD][/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$950.00[/TD]
[TD]$487.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jproffer]

So what i want to be able to do is when i enter a price in A2 it will automatically put the outlay amount in cell B2

Ok, so how is the outlay determined? Or where does it come from? I see the "bet" on the right...is that where you want to find the outlay?

Second part is pretty easy. In E2, put:

=IF(C2="1st",D2,0)

and drag down however far you need to

 

[Michael M]

you will need to provide more information.
How are the figures in col B and C derived ??

 

[jabbaman]

Ok, so how is the outlay determined? Or where does it come from? I see the "bet" on the right...is that where you want to find the outlay?

Second part is pretty easy. In E2, put:

=IF(C2="1st",D2,0)

and drag down however far you need to

Hi, and thanks for responding,
The outlay is determined by the price which is in I1,J1 and K1
Example is between $1.00 to $2.00 is a $250 bet
$2.01 to $3.00 is a $200 bet and so on.

 

[jabbaman]

Hi, thanks for the reply
Col B is the outlay price which is determined by the win price and Col C is where the horse finished.

 

[Michael M]

To write a constructive formula we will need ALL the details

and so on

is not quite adequate, i'm afraid.
and what happens when the horse finishes 2nd ??

 

[jabbaman]

To write a constructive formula we will need ALL the details

is not quite adequate, i'm afraid.
and what happens when the horse finishes 2nd ??

Hi Michael,
I,ve just about got it all worked out bar one thing which is how to automatically enter a win price which will automatically put the outlay price in.

Win Price Outlay
$1.55 $250.00
$1.80 $250.00
$2.70 $200.00
$7.00 $100.00
$3.40 $150.00

Prices Min Prices Max Bet
$1.00 $2.00 $250
$2.01 $3.00 $200
$3.01 $4.00 $150
$4.01 $150.00 $100
So if the price is $1.50 = $250
$2.70 = $200
$3.15 = $150
Over $4.01 is $100
Hope that help

 

[jabbaman]

Hi jproffer,

Yes the outlay comes from Bet cells and i have min and max price cells, as you can see if the price is $1.55 i put $250 in. If price is $2.85 i put $200 in and so forth.

 

[Michael M]

So how can an outlay of $250 be both $1.55 AND $1.80

 

[jabbaman]

Hi Michael,

If the price is between $1.00 and $2.00 its a $250 bet if $2.01 to $3.00 its a $200, $3.01 to $4.00 is $150 and anything over $4.01 is $100 bet.