+ Reply to Thread
Results 1 to 6 of 6

DIV and VALUE error on ROUND AVERAGE and IF ABS statement

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

    DIV and VALUE error on ROUND AVERAGE and IF ABS statement

    I'm having a couple issues with formulas which i'm not smart enough to solve, I hope someone will be able to help me

    Explanation:
    K to N is filled manually
    O is the average of K and M with round 0, formula =IF(COUNTA(K4;M4)=2;ROUND(AVERAGE(K4;M4);0);"")
    P is the average of L and N with round 1 decimal, add /10 to the end, formula =ROUND(AVERAGE(L4;N4);1)&"/10"
    Q is the difference between K and M with ABS, IF=0 leave blank, formula IF(ABS([K]-[M])=0;"";ABS([K]-[M]))
    R is the difference between L and N with ABS, IF=0 leave blank, formula IF(ABS([L]-[N])=0;"";ABS([L]-[N]))

    EDIT: Sorry for the weird Q and R statements, the K means coloumn K and so on.

    Screenshot 2022-01-14 at 18.16.49.png

    The following errors occours:
    P displays error DIV/0 if L and N is blank, P should remain blank in this case
    Q displays error VALUE if K or M is N/A, Q should remain blank in this case
    R displays error VALUE if L or N is N/A, R should remain blank in this case

    Hope everyone has a great weekend!
    Last edited by Theguyfromthething; 01-14-2022 at 01:36 PM.

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

    Re: DIV and VALUE error on ROUND AVERAGE and IF ABS statement

    Seems I'm unable to post my Q and R formulas, I'll try here:

    Explanation:
    K to N is filled manually
    O is the average of K and M with round 0, formula =IF(COUNTA(K4;M4)=2;ROUND(AVERAGE(K4;M4);0);"")
    P is the average of L and N with round 1 decimal, add /10 to the end, formula =ROUND(AVERAGE(L4;N4);1)&"/10"
    Q is the difference between K and M with ABS, IF=0 leave blank, formula IF(ABS([K]-[M])=0;"";ABS([K]-[M]))
    R is the difference between L and N with ABS, IF=0 leave blank, formula IF(ABS([L]-[N])=0;"";ABS([L]-[N]))

    EDIT: Sorry for the weird Q and R statements, the K means coloumn K and so on.

  3. #3
    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
    44,002

    Re: DIV and VALUE error on ROUND AVERAGE and IF ABS statement

    Brrrr. Too many words and a picture.


    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    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

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

    Re: DIV and VALUE error on ROUND AVERAGE and IF ABS statement

    Should be attached now Glenn
    Attached Files Attached Files
    Last edited by Theguyfromthething; 01-14-2022 at 03:51 PM. Reason: Wrong attachment.

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

    Re: DIV and VALUE error on ROUND AVERAGE and IF ABS statement

    Sorry for the wall of text, please see my other reply with attachment.

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

    Re: DIV and VALUE error on ROUND AVERAGE and IF ABS statement

    I worked it out. DIV error could be fixed with IFERROR statement: =IFERROR(ROUND(AVERAGE(M2;O2);1)&"/10";"")
    Value Error fixed with: =IF(OR(L3="";N3="");"";IFERROR(ABS(L3-N3);""))

+ 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] round with if statement
    By Mark-44 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-24-2016, 06:57 AM
  2. [SOLVED] If and round statement
    By TUFXP in forum Excel General
    Replies: 4
    Last Post: 11-22-2015, 06:23 PM
  3. Need to add a round function to an average
    By Fenguitar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-25-2013, 02:05 PM
  4. round average
    By jynrlpz653 in forum Excel General
    Replies: 2
    Last Post: 11-23-2012, 04:43 PM
  5. Round function in If statement
    By penri0_0 in forum Excel General
    Replies: 3
    Last Post: 05-25-2006, 07:55 AM
  6. Round in an IF statement
    By heater in forum Excel General
    Replies: 6
    Last Post: 08-03-2005, 12:05 AM
  7. IF-then statement w/ an average and a #DIV/0! error
    By johnwspears in forum Excel General
    Replies: 4
    Last Post: 04-13-2005, 01:57 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