Question description:
This user has given permission to use the problem statement for this
blog.
I need an excel formula for my horse racing results spreadsheet. There are four columns - BSP, Low, Win and Lay. The figures for the BSP and Low columns are entered manually. If the figure in the Low column reads 1.01 the formula will take the figure in the BSP column, multiply it by 10, then minus 10 and display it as a minus figure in the Lay column. It will then take the figure in the lay column, deduct 6.5% and then place that in the Win column. Example - BSP is 6, Low is 1.01, then Lay column will read minus 50 and Win column would read 46.75 (50 minus 6.5%),
Solved by K. H. in 13 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
19/10/2017 - 12:13
Hello! Thanks for choosing Got It Pro! I will help you today.
Excelchat Expert
19/10/2017 - 12:14
Do you have the file that you want to change?
User
19/10/2017 - 12:15
no
Excelchat Expert
19/10/2017 - 12:15
Ok, I will create an example here
User
19/10/2017 - 12:15
ok
Excelchat Expert
19/10/2017 - 12:16
When it is not 1.01, what do you want it to show for Lay?
User
19/10/2017 - 12:16
9.35
User
19/10/2017 - 12:16
win would read minus 10
Excelchat Expert
19/10/2017 - 12:20
All set!
Excelchat Expert
19/10/2017 - 12:20
Can you check if this is what you need?
User
19/10/2017 - 12:22
If the number 3 was in column B, would it work the same
Excelchat Expert
19/10/2017 - 12:22
Exaclty, any number different than 1.01
Excelchat Expert
19/10/2017 - 12:22
I changed row 3 so you can see it
User
19/10/2017 - 12:23
Thanks, that was amazing. Response time was great
Excelchat Expert
19/10/2017 - 12:23
Great! Do you have any questions?
Excelchat Expert
19/10/2017 - 12:23
Do you need anything else?
User
19/10/2017 - 12:23
How do i get the formula
Excelchat Expert
19/10/2017 - 12:23
Both are IFs formulas
Excelchat Expert
19/10/2017 - 12:24
Lay, for example
Excelchat Expert
19/10/2017 - 12:24
=if(B2=1.01,-ABS((A2*10)-10),9.35)
Excelchat Expert
19/10/2017 - 12:24
If B2 (Low) is equal to 1.01, it takes A2 (BSP) times 10 minus 10
Excelchat Expert
19/10/2017 - 12:25
ABS returns the value with no signs, and then I placed a minus sign before it
User
19/10/2017 - 12:25
thanks, i just copied the formulas
Excelchat Expert
19/10/2017 - 12:25
-ABS((A2*10)-10)
Excelchat Expert
19/10/2017 - 12:25
If it's different, it returns 9.35
Excelchat Expert
19/10/2017 - 12:25
Oh, great
Excelchat Expert
19/10/2017 - 12:25
Then you just paste it and drag the formula down
User
19/10/2017 - 12:25
Do you need anything from me now
Excelchat Expert
19/10/2017 - 12:26
No, that's all
Excelchat Expert
19/10/2017 - 12:26
Have a great day!
User
19/10/2017 - 12:26
Appreciate all your help, have a great day, thanks again. Will tell all my friends about your site
Excelchat Expert
19/10/2017 - 12:26
Thank you
Excelchat Expert
19/10/2017 - 12:27
:)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.