IF Error/Vlookup Help

excelexcelexcel123

New Member
Joined
Jul 1, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I am trying to use a vlookup to calculate averages across multiple sheets. I think I am close to getting the formula right, my only issue is records in Column A on my summary sheet are not returning a value if the record is not on both sheets. Cell B5 for example. What am I missing here? Thanks in advance.
 

Attachments

  • 2021-07-01 16_12_28-Excel.png
    2021-07-01 16_12_28-Excel.png
    34.9 KB · Views: 15

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,012
Office Version
  1. 365
Platform
  1. Windows
Your formula has a lookup to Jun 23 and one to Jun 24. If either lookup can't find the value then it that look up will return #N/A.
The Average will return #N/A if either of the 2 lookups returns #N/A.
The IfError will then take over and return ""

You need to decide:
  • Is the average calculation valid if either value is not found ?
    If not then returning blank is fine.
  • If you do consider it is still valid what calculation do you want to use if
    - Jun 23 is not found
    - Jun 24 is not found
    eg if you just wanted to set the not found one to 0
    =average(Iferror(vlookup(A3,Jun23,6,0),0),iferror(A3,Jun24,6,0))
  • Note: If you wanted to have different combinations you might need to fall back to using If(ISNA(expression),true,false) or if (ISERROR(expresssion),true,false)
 

excelexcelexcel123

New Member
Joined
Jul 1, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thanks for the response. To provide a little more detail, I am trying to rates of a certain outcome of phone calls. The value in B5 on the summary sheet should be 12.06, as that was the rate on June 23. On June 24, there were no phone calls made to this particular record, so the average should still be 12.06. That is where I am running into the problem.
 

excelexcelexcel123

New Member
Joined
Jul 1, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I cannot seem to edit my post, but this eventually needs to be done for an entire month, which is why I'm trying to use a fairly complicated (for me at least) formula to calculate just 2 days.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,012
Office Version
  1. 365
Platform
  1. Windows
This is going to get much more complicated.
  • Is each day going to be in a separate sheet ?
  • Is it the case that every time a value is missing, it means no price change and you want to bring forward the previous value ?
  • Also your image is only showing results as percentages which doesn't seem consistent with the nature of the exercise.
You may need a Power Query or VBA solution to do this.
 

Forum statistics

Threads
1,143,677
Messages
5,720,254
Members
422,273
Latest member
linds75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top