AND and IF formulas?

**WGBarry** · 04-19-2013, 06:56 AM

Hi guys I am wondering if anyone can be so kind enough to help me out with what I am finding a pretty complicated formula, I have used excel for years but only now starting to use anywhere near its full functions.

I will explain the spreadsheet as well as I can. Firstly the spread sheet tracks race horses and there are a lot of them. 18706 since 15th of Feb to be exact!

In column AZ is the price that the horse bet price i.e. 6.4 and then in column BB is the position that horse came in the race i.e. 1st, 2nd, 3rd etc.

In column BC is the stake on the horse, this is always 1 in this case and in column BD I would like the formula to firstly ask if the horse has won the race i.e. been in position 1 and then if it has been in position 1 then do the sum (BC*AZ-BC) and if it does not win then do the sum (0-1). I am sure all you excel experts will find that very simple, but this is when it gets harder, and a lot harder for me sadly.

In column BF I would like the same thing only I want it to firstly find out if the number in column AZ is between 2 and 2.99. if it is not then I would like it to be filled with 0. If it does meet the number range then I would like it to ask if it is in position 1 then do the sum (BE*AZ-BE) and if it does not win then do the sum (0-1). If someone (anyone!) could be kind enough to show me to there I will no doubt be able to do it myself up until the end of the rest of the number ranges from 3 up to 20!

All and any help will be much appreciated!

**JosephP** · 04-19-2013, 07:49 AM

I reckon

=IF(BB2=1,BC2*AZ2,0)-BC2
and
=IF(AND(AZ2>=2,AZ2<3),IF(BB2=1,BC2*AZ2,0)-BC2,0)

**WGBarry** · 04-19-2013, 08:14 AM

Thank you for the input Joseph.

The first formula works to get the negative numbers i.e. when a horse doesnt win the value in the cell becomes -1 but when the position is 1st the value becomes #NAME?

The second one sort of worked and did exactly what I asked for help doing, but I actually wanted to do something else HAHA but it has helped me work it out on my own!

**JosephP** · 04-19-2013, 08:16 AM

did you copy and paste the formula or type it yourself?

**WGBarry** · 04-19-2013, 08:27 AM

I have just typed it myself Joseph and that is working magically, thank you so much for your help, you have probably saved me an hour or 2 work every morning now!