+ Reply to Thread
Results 1 to 4 of 4

If both cells has values, get average and add "/10"

  1. #1
    Registered User
    Join Date
    01-13-2022
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    7

    Question If both cells has values, get average and add "/10"

    If coloumns L+N has values, I need to get an average of these, and if possible add "/10" at the end if the cell is filled out. I have tried for hours and either get an error or /10 written in the blank cells. The result in the screenshot is not desirable either as the formula doesn't account for blank cells such as L3.

    My current formula is:
    =IFS((L3+N3)/2=0;"";(L3+N3)/2;(L3+N3)/2)

    Screenshot 2022-01-13 at 18.48.30.png

    I managed to get the /10, but can't for the life of me figure out how to get rid of it if no values are in the coloumns L+N:
    =IF((L27+N27)/2=0;"";(L27+N27)/2)&"/10"

    Screenshot 2022-01-13 at 18.57.33.png

    Any help is greatly appreciated!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: If both cells has values, get average and add "/10"

    Your explanation is incomplete... what do you want to happen if only ONE of the cells is populated:

    =IF(COUNTA(L3:M3)=2,AVERAGE(L3:M3)&"/10","")

    This will return a blank, under those circumstances.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    01-13-2022
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    7

    Re: If both cells has values, get average and add "/10"

    This works wonders! If possible I'd like to display the average of whatever amount of the two cells are filled. So if N3 is filled but L3 is not, simply divide by 1. This could probably be done with just displaying whatever number is filled.

    Thanks for your time!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: If both cells has values, get average and add "/10"

    Then use:

    =AVERAGE(L3:M3)&"/10"

+ 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] Brief formula to convert time values to "shift1", "shift2", "shift3".
    By darekpawel in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 04-11-2019, 07:05 AM
  2. Replies: 0
    Last Post: 02-14-2018, 06:14 PM
  3. [SOLVED] How can I copy "=Average(A1:A2)" and paste as "=Average(A3:A4)" in next space in column?
    By matt_m_is_me in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-27-2014, 11:02 AM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. Replies: 3
    Last Post: 04-14-2013, 11:53 PM
  6. [SOLVED] Pasting values between ranges in different workbooks: "Range" works but "Cells" doesn't
    By Flaubert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2013, 01:19 PM
  7. Replies: 3
    Last Post: 02-16-2011, 02:55 PM

Tags for this Thread

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