|
|
Post by SwoopingMagpie on Oct 13, 2021 3:21:24 GMT -5
Hi guys, I'm creating an excel spreadsheet to calculate my scores playing in tournaments and regular play to track my improvement. I have done a little work on it and due to the fact I don't really know a lot about excel I cannot figure out how to create certain functions.
A B C D E
So you can see that I have worked out certain things........However, I would like the 0 in column E to show up as an E because the course par
written in white is "72".
Code written into B2 is =IF(B1="","",(B1-$A1))
So I'd Like to know how to get the E when even par and also how to write into the code if possible a plus sign when over par as I can't use
number format tool as it doesn't have it in excel online.
The code for A2 is =SUM(B2:E2) and if I was even par or over par I would like the same result in this cell if the sum was to return that sort
of result.
Thanks in advance for any response.
|
|
|
|
Post by schmiff on Oct 13, 2021 9:37:53 GMT -5
In B2, try =IF(B1=$A1,"E",IF(B1="","",(B1-$A1)))
(and copy across)
|
|
|
|
Post by SwoopingMagpie on Oct 13, 2021 10:08:30 GMT -5
In B2, try =IF(B1=$A1,"E",IF(B1="","",(B1-$A1))) (and copy across) That worked perfectly for the even par statement, do you have any idea how to add the plus sign for over par?? Thanks for the help!! |
|
|
|
Post by schmiff on Oct 13, 2021 12:13:28 GMT -5
**ignore this - see post below** Normally it would be a custom number format, but if those aren't available in Excel Online I can only think of a horrible hack to get around it, which would be to add a third row where the formula would be (in B3): =IF(AND(B2>0,ISNUMBER(B2)),"+"&B2,B2) This will represent a score over par with a plus sign in front of it, however it won't be a number format (but text instead) so can't be used in calculations. The ISNUMBER bit is required because Excel thinks E is more than zero, so you end up with +E otherwise. If you then set A3 to =A2 you can hide row 2 altogether. There might be a more graceful way but I can't think of one for now  |
|
|
|
Post by schmiff on Oct 13, 2021 12:33:13 GMT -5
Scratch that, found a better way Change your row 2 formula to: =IF(B1-$A1=0,"E",IF(B1-$A1>0,"+"&B1-$A1,B1-$A1)) That does it all in one go. Then change A2 to: =SUM(B1:E1)-($A1*COUNT(B1:E1)) so it isn't dependent on weird text cells |
|
|
|
Post by SwoopingMagpie on Oct 13, 2021 17:47:21 GMT -5
Scratch that, found a better way Change your row 2 formula to: =IF(B1-$A1=0,"E",IF(B1-$A1>0,"+"&B1-$A1,B1-$A1)) That does it all in one go. Then change A2 to: =SUM(B1:E1)-($A1*COUNT(B1:E1)) so it isn't dependent on weird text cells Thanks for your work, unfortunately the second code is ignoring anything with a plus and as soon as i put in an under par score it returns the par score with a minus sign.  Sorry about this, if it can't be done then so be it, thanks for your help so far though. |
|
|
|
Post by schmiff on Oct 14, 2021 5:53:14 GMT -5
It works for me - make sure your 'total' cell (S5?) is looking at the row where you're entering the scores and not the row underneath. However the total is missing a plus sign where necessary, so change it to: =IF(SUM(B1:E1)-($A1*COUNT(B1:E1))>0,"+"&SUM(B1:E1)-($A1*COUNT(B1:E1)),SUM(B1:E1)-($A1*COUNT(B1:E1))) (replace B1:E1 with the cell range you're entering scores into) Image of it working: drive.google.com/file/d/1vfiZrmR25jMgaU1pg9hjFPL9A7T_sJv2/view?usp=sharing |
|
|
|
Post by SwoopingMagpie on Oct 15, 2021 6:13:34 GMT -5
It works for me - make sure your 'total' cell (S5?) is looking at the row where you're entering the scores and not the row underneath. However the total is missing a plus sign where necessary, so change it to: =IF(SUM(B1:E1)-($A1*COUNT(B1:E1))>0,"+"&SUM(B1:E1)-($A1*COUNT(B1:E1)),SUM(B1:E1)-($A1*COUNT(B1:E1))) (replace B1:E1 with the cell range you're entering scores into) Image of it working: drive.google.com/file/d/1vfiZrmR25jMgaU1pg9hjFPL9A7T_sJv2/view?usp=sharingIt worked this time!! Thanks for the help with all of this.......I'm wondering it it could be possible for that same cell to show nothing when there is nothing in the cells to calculate and if it possible for the cell to return an E when the total is even par? Thanks for all you have done. |
|
|
|
Post by schmiff on Oct 15, 2021 12:59:30 GMT -5
Here you go!
=IF(COUNT(B1:E1)=0,"",IF(SUM(B1:E1)-($A1*COUNT(B1:E1))=0,"E",IF(SUM(B1:E1)-($A1*COUNT(B1:E1))>0,"+"&SUM(B1:E1)-($A1*COUNT(B1:E1)),SUM(B1:E1)-($A1*COUNT(B1:E1)))))
|
|
|
|
Post by SwoopingMagpie on Oct 16, 2021 0:31:04 GMT -5
Here you go! =IF(COUNT(B1:E1)=0,"",IF(SUM(B1:E1)-($A1*COUNT(B1:E1))=0,"E",IF(SUM(B1:E1)-($A1*COUNT(B1:E1))>0,"+"&SUM(B1:E1)-($A1*COUNT(B1:E1)),SUM(B1:E1)-($A1*COUNT(B1:E1))))) Thanks schmiff, It all works now......Your a legend thanks again!!! |
|
