+ Reply to Thread
Results 1 to 15 of 15

Range including *

  1. #1
    Registered User
    Join Date
    08-15-2016
    Location
    Minneapolis
    MS-Off Ver
    2007
    Posts
    11

    Range including *

    I am making a scoring sheet, which will allow the user to score skills on a range of 1-5. I set the range and that works fine, but now we want to use something like '*' to indicate not scored. I had used a 0 for not scored, but since we are averaging the scores that doesn't work.

    Is there a way to set a range of 1-5, but also accept something like '*'?

    Thanks very much.

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Range including *

    Hi Bobelmore,

    Excel would not use the * value in cells to adjust the average so you can use this as your N/A value.

    What are you using to create you range?

    Named ranges? Data Validation?

    I think we might need an example worksheet to see your problem as I can't work out what the issue actually is

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,975

    Re: Range including *

    Assuming your scores are in A1 to A5 =AVERAGE(A1:A5) wioll work, even if there is a * in the range. Did it not work when you tried it??
    Glenn



  4. #4
    Registered User
    Join Date
    08-15-2016
    Location
    Minneapolis
    MS-Off Ver
    2007
    Posts
    11

    Re: Range including *

    DataVal.PNG

    I am using Data Validation to restrict the input to whole numbers 1-5. I don't see a way to allow the asterisk.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,975

    Re: Range including *

    Set them up as a list and use that as DV
    Attached Files Attached Files

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Range including *

    Do you want to allow only 1,2,3,4,5, or *?
    If posting code please use code tags, see here.

  7. #7
    Registered User
    Join Date
    08-15-2016
    Location
    Minneapolis
    MS-Off Ver
    2007
    Posts
    11

    Re: Range including *

    Using a list is a good idea, but the asterisk is then counted as zero when averaging.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,975

    Re: Range including *

    No it isn't...
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-15-2016
    Location
    Minneapolis
    MS-Off Ver
    2007
    Posts
    11

    Re: Range including *

    Glenn, thank you. I looked at your example, but that is not how I had mine set up.

    I have attached an example of what I am trying to do. It works fine, except that I cannot enter an asterisk, or any other character to signify not yet scored.
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,975

    Re: Range including *

    Now you can enter an asterisk....

    it will aceept anything from the Named Range called "List". "list" is defined as A1 to A6 on Sheet2. Hit CTRL-F3 to view the named range. You can hide sheet2, if you wish.
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Range including *

    Thanks for continuing with this Glenn, I suspected it was to do with data validation as the OP was steering us towards the calculation being an issue which I wouldn't expect it to be.

    Bobelmore hopefully this now has resolved your issue?

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,975

    Re: Range including *

    Who knows.... Bobelmore disapearred a few minutes after my last post...

  13. #13
    Registered User
    Join Date
    08-15-2016
    Location
    Minneapolis
    MS-Off Ver
    2007
    Posts
    11

    Re: Range including *

    Hello, and thanks for all your help.

    Your example works exactly as I had hoped. However, when I used the same approach in my scoring sheet the asterisk is still being computed as zero. I am trying to figure out how they are different.

    Thanks.

  14. #14
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Range including *

    Quote Originally Posted by bobelmore View Post
    However, when I used the same approach in my scoring sheet the asterisk is still being computed as zero.
    Can you post a small sample file that demonstrates this?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,975

    Re: Range including *

    In your previously posted example you were using SUM and dividing by 6. If you are still using that, you will not get it to work. I suggested that you use the AVERAGE function: which works perfectly. If it isn't working: have you accidentally reset calculation options to manual?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Including a Range in Formula, Less a Cell
    By alexiwcd in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-22-2015, 01:16 PM
  2. [SOLVED] including a date range in SUMIF
    By Emma Blower in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2014, 05:24 PM
  3. Function including a variable range
    By ARayburn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2013, 10:50 AM
  4. [SOLVED] Sumifs including date range
    By polecat1234 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2012, 10:42 PM
  5. [SOLVED] How to copy a range (including any formulas in the range) into an array?
    By jimmalk in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 11-28-2012, 01:51 AM
  6. How to make one range equal EXACTLY another range including formats
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2011, 06:52 PM
  7. Why would a Range including over 524 rows not work?
    By jebediah in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2007, 02:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1