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]

 

[Michael M]

So this in B2 and drag down

=IF(A2<=2,250,IF(A2<=3,200,IF(A2<=4,150,100)))

 

[jabbaman]

You are a legend Michael, thanks very much i really appreciate it.

 

[Michael M]

And just in case A2 is blank

=IF(A2="","",IF(A2<=2,250,IF(A2<=3,200,IF(A2<=4,150,100))))

 

[jabbaman]

Cheers Michael

 

[jabbaman]

Hi Michael,
I have one more formula i need help with, i think it might a IF formula not sure. So A2 has dog names, B2 has trainers name, i also have columns with each trainers dogs, so what i would like to do is once i put the dogs name in A2 it automatically put the trainers name in cell B2. Hope you can help, thanks.

[TABLE="width: 1403"]
<colgroup><col span="2"><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]TIPPERARY MEL[/TD]
[TD]Sam Sultana[/TD]
[TD]Ashlee Terry[/TD]
[TD]BIG REPUTATION[/TD]
[TD]Brooke Ennis[/TD]
[TD]BAROOGA BRETT[/TD]
[TD]Kevin Eyles[/TD]
[TD]BIG BARRA[/TD]
[TD]Darren Brown[/TD]
[TD]CHIEF'S EMPIRE[/TD]
[TD]Seona Thompson[/TD]
[TD]ASTON DEE BEE[/TD]
[/TR]
[TR]
[TD]MINI DIVA[/TD]
[TD]Sam Sultana[/TD]
[TD][/TD]
[TD]BRIGHT NEW SHINE[/TD]
[TD][/TD]
[TD]BELLA SHIMA[/TD]
[TD][/TD]
[TD]HEARTBREAK HERMY [/TD]
[TD][/TD]
[TD]CHIEF'S SIDEKICK[/TD]
[TD][/TD]
[TD]ASTON DUKE[/TD]
[/TR]
[TR]
[TD]YODA[/TD]
[TD]Brooke Ennis[/TD]
[TD][/TD]
[TD]BURN LIKE FIRE[/TD]
[TD][/TD]
[TD]BELT UP BUBBS[/TD]
[TD][/TD]
[TD]KRAKEN AYE KAY[/TD]
[TD][/TD]
[TD]MAYOR DIGBY[/TD]
[TD][/TD]
[TD]AZKABAN[/TD]
[/TR]
[TR]
[TD]BAROOGA BRETT[/TD]
[TD]Brooke Ennis[/TD]
[TD][/TD]
[TD]CHARMED AN TAKEN[/TD]
[TD][/TD]
[TD]MIGHTY HAZZA[/TD]
[TD][/TD]
[TD]KRAKEN BAM BAM [/TD]
[TD][/TD]
[TD]MINI DIVA[/TD]
[TD][/TD]
[TD]BECKHAM[/TD]
[/TR]
[TR]
[TD]INVASION[/TD]
[TD]Karen M Walsh[/TD]
[TD][/TD]
[TD]EYE GOT IT[/TD]
[TD][/TD]
[TD]MY MATE LENNY[/TD]
[TD][/TD]
[TD]KRAKEN BOLT[/TD]
[TD][/TD]
[TD]PERCY'S CURSE[/TD]
[TD][/TD]
[TD]BEWILDERING[/TD]
[/TR]
[TR]
[TD]THRILLING ARNOLD[/TD]
[TD]Karen M Walsh[/TD]
[TD][/TD]
[TD]EYES ON YOU[/TD]
[TD][/TD]
[TD]PANDAKEN[/TD]
[TD][/TD]
[TD]KRAKEN GRETA[/TD]
[TD][/TD]
[TD]PERCY'S EMPIRE[/TD]
[TD][/TD]
[TD]DESPACITO[/TD]
[/TR]
[TR]
[TD]RIDIN' SHOTGUN[/TD]
[TD]Karen M Walsh[/TD]
[TD][/TD]
[TD]LEICA CALI KING[/TD]
[TD][/TD]
[TD]ROCKSTAR STRATUS[/TD]
[TD][/TD]
[TD]KRAKEN PACE[/TD]
[TD][/TD]
[TD]YOUR HANGER[/TD]
[TD][/TD]
[TD]DOUBLE QUE[/TD]
[/TR]
[TR]
[TD]HOT PLATTER[/TD]
[TD]Johannes Vanderburg[/TD]
[TD][/TD]
[TD]MY AMERICAN GIRL[/TD]
[TD][/TD]
[TD]STERLING ARCHER[/TD]
[TD][/TD]
[TD]MI ROMA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EQUANIMITY[/TD]
[/TR]
[TR]
[TD]DIDDILEE[/TD]
[TD]Johannes Vanderburg[/TD]
[TD][/TD]
[TD]NERVOUS AN WEIRD[/TD]
[TD][/TD]
[TD]TRITT TRITT[/TD]
[TD][/TD]
[TD]WHAT STOPPING YA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EXTREME MAGIC[/TD]
[/TR]
[TR]
[TD]KRAKEN PACE[/TD]
[TD]Kevin Eyles[/TD]
[TD][/TD]
[TD]OUTTA MY DEPTH[/TD]
[TD][/TD]
[TD]TRUMPSTA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FAB REIKO[/TD]
[/TR]
[TR]
[TD]KRAKEN BOLT[/TD]
[TD]Kevin Eyles[/TD]
[TD][/TD]
[TD]PURE WHITE EVIL[/TD]
[TD][/TD]
[TD]YODA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FLYING SPARKLES[/TD]
[/TR]
[TR]
[TD]KRAKEN AYE KAY[/TD]
[TD]Kevin Eyles[/TD]
[TD][/TD]
[TD]QUENA DEE AIR[/TD]
[TD][/TD]
[TD]ZIPPING ANGUS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]HEMSWORTH[/TD]
[/TR]
[TR]
[TD]KRAKEN BAM BAM[/TD]
[TD]Kevin Eyles[/TD]
[TD][/TD]
[TD]SHAKIN' BRAVE[/TD]
[TD][/TD]
[TD]ZIPPING NANCY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OUT OF RANGE[/TD]
[/TR]
[TR]
[TD]HEARTBREAK HERMY[/TD]
[TD]Kevin Eyles[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]POKE THE BEAR[/TD]
[/TR]
[TR]
[TD]PERCY'S EMPIRE[/TD]
[TD]Darren Brown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REIKO ROCKETTA[/TD]
[/TR]
[TR]
[TD]CHARMED AN TAKEN[/TD]
[TD]Ashlee Terry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]STYGIAN[/TD]
[/TR]
[TR]
[TD]TRUE TALENT[/TD]
[TD]Seona Thompson[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TRUE TALENT[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Do you have a lookup list for trainers / dog names ?
If so, where is it ?
If not, how do we know who trains which dog to start with??

 

[jabbaman]

Hi Michael,

no i haven't got a lookup list. All i have is their name in one cell and their dogs in the next cell.
Example:
Kevin Eyles BIG BARRA

HEARTBREAK HERMY
KRAKEN AYE KAY
KRAKEN BAM BAM
KRAKEN BOLT
KRAKEN GRETA
KRAKEN PACE
MI ROMA
WHAT STOPPING YA

 

[Michael M]

OK, is the name of the trainer next to each dog ??
like this

Excel 2007
	A	B
1	Kevin Eyles	BIG BARRA
2	Kevin Eyles	HEARTBREAK HERMY
3	Kevin Eyles	KRAKEN AYE KAY
4	Kevin Eyles	KRAKEN BAM BAM
5	Kevin Eyles	KRAKEN BOLT
6	Kevin Eyles	KRAKEN GRETA
7	Kevin Eyles	KRAKEN PACE
8	Kevin Eyles	MI ROMA
9	Kevin Eyles	WHAT STOPPING YA
Sheet1

 

[jabbaman]

Hi Michael, yes but i only have his name once in A1 then the dogs the way you have it. but i have a few different trainers , so another trainers name in C1 then dogs name D1 etc etc E1, F1 and so on.

 

[Michael M]

with 'em all side by side it's gonna be difficult to write a simple formula.
I would suggest a lookup table, either somewhere on the same worksheet OR on it's own...and then use a VLOOKUP formula
The table should look like this

Excel 2007
	D	E
1	BIG REPUTATION	Ashlee Terry
2	BRIGHT NEW SHINE	Ashlee Terry
3	BURN LIKE FIRE	Ashlee Terry
4	CHARMED AN TAKEN	Ashlee Terry
5	EYE GOT IT	Ashlee Terry
6	EYES ON YOU	Ashlee Terry
7	LEICA CALI KING	Ashlee Terry
8	MY AMERICAN GIRL	Ashlee Terry
9	NERVOUS AN WEIRD	Ashlee Terry
10	OUTTA MY DEPTH	Ashlee Terry
11	PURE WHITE EVIL	Ashlee Terry
12	QUENA DEE AIR	Ashlee Terry
13	SHAKIN' BRAVE	Ashlee Terry
14	BAROOGA BRETT	Brooke Ennis
15	BELLA SHIMA	Brooke Ennis
16	BELT UP BUBBS	Brooke Ennis
17	MIGHTY HAZZA	Brooke Ennis
18	MY MATE LENNY	Brooke Ennis
19	PANDAKEN	Brooke Ennis
20	ROCKSTAR STRATUS	Brooke Ennis
21	STERLING ARCHER	Brooke Ennis
22	TRITT TRITT	Brooke Ennis
23	TRUMPSTA	Brooke Ennis
24	YODA	Brooke Ennis
25	ZIPPING ANGUS	Brooke Ennis
26	ZIPPING NANCY	Brooke Ennis
27	BIG BARRA	Kevin Eyles
28	HEARTBREAK HERMY	Kevin Eyles
29	KRAKEN AYE KAY	Kevin Eyles
30	KRAKEN BAM BAM	Kevin Eyles
31	KRAKEN BOLT	Kevin Eyles
32	KRAKEN GRETA	Kevin Eyles
33	KRAKEN PACE	Kevin Eyles
34	MI ROMA	Kevin Eyles
35	WHAT STOPPING YA	Kevin Eyles
Sheet2

Then a VLOOKUP formula like

=Vlookup(A1,$D$1:$E$35,2,0)

so the formula looks up a value equivalent to A1 in the table of D1:E35, and returns the equivalent value in the 2nd column of that table....does that help / make sense ???