1. Y

    How to find max value with empty cells & #div/0!

    Hello, I have inlcuded an example chart that I use regularly for work below (assuming it starts in cell A1 - so data range would be B2:AH34). Is there a way to find the max value of this chart, or even the top 5 max values (would be ideal but will settle for just one max figure for now if it's...
  2. D

    Eliminate #DIV/0! with IFERROR

    Hi there I'm having a brain freeze with an IFERROR to eliminate the #DIV/0! after a simple calculation and it's driving me insane... the formula is =(E108-C108)/C108 the result of course where there are zeros is #DIV/0!. I have tried a few variations unsuccessfully any help will be greatly...
  3. D

    How to ignore #DIV/0! error

    Array formula is {=AVERAGE(IF(ISNUMBER(MATCH(I13:I18,SMALL(I13:I18,{1}),0)),(H13:H18)))}. Want to ignore the #DIV/0! error and use the cell in column H corresponding to the I column having the #DIV/0! error. TIA
  4. M

    Finding Percentages Between Two Cells and Ignoring Empty Rows

    Hey Everyone, I'm trying to get the average between two cells using this formula: =AT2/AU2 (I'm using a new column, AV2 for the formula) Unfortunately, I only get "1" as an answer when comparing cells with values in them, even if AT2=100 and AU2=4,562,346. On top of that, if AT3, and AU3...
  5. I

    divide by zero error help

    HI guys i have set the below formula ( with some help from mrexcel members !) below to pull some numbers but i get the #DIV/0! error sometimes do to the 0 or no data. below is the formula...
  6. I

    how can i get rid of #DIV/0! error

    his guys i have the below formula which pics up the average day from a different sheet IF(AND(MONTH(MAX('Days'!$Q:$Q))=5,YEAR(MAX('Days'!$Q:$Q))=2019),AVERAGEIFS('Days'!$S:$S,'Average Lead Days'!$E:$E,$D17,'Days'!$R:$R,"Qualified"),"-") however when there is no data it comes back with a...
  7. W

    removing #DIV/0! & #VALUE!

    What do I need to do to this equation, so I don't get the #DIV/0! result? =IF(E43<=(N43/2),((A25/(ROUNDDOWN((N43/E43),0)))*B43),A25*B43) Same with #VALUE ! =IFERROR((D25/(ROUNDDOWN((O43/H43),0))),"")*B43 Thanks
  8. B

    #Div/0! issue

    HI All, I have slightly long formula but i have some issues with #Div/0! fault in some of the returns. Do you know how I can adjust formula to return 0 in case of issue ?:confused...
  9. M

    How to get rid of #DIV/0! Office 2013

    Here is the formula: =AVERAGEIF(Table1[Customer '#],'Customer Summary'!$A$2:$A$50000,Table1[Days I tried =IFERROR(AVERAGEIF(Table1[Customer '#],'Customer Summary'!$A$2:$A$50000,Table1[Days],"") it will not calculate.
  10. J

    Power Query from Google Sheets results in #DIV/0

    Hi everyone, I'm new to this forum. I use Excel on a daily basis for work and have recently started playing around with Power Query for pulling in data from the internet. Haven't been able to find any info on this specific problem that I've been having. Google Sheets has a handy finance...
  11. O

    If, Or, IFERROR statement

    Good morning. I am trying to write the following, and I keep receiving a #DIV/0 error or the formula just errors: If D2=0 or D2/E2<1.5 then "Yes" should populate otherwise, the cell should be blank. Any help is appreciated.
  12. R

    How to stop 0% being a #DIV/0

    So I'm just doing a simple formula of B1/A1 as a percentage. If B1 is 0 then the answer reverts to an error (#DIV/0). How do I get it to be BLANK if answer is zero? Please help - thanks in advance
  13. J

    daily, MTD and YTD Sales Report

    I am trying to create a Sales Report that will show performance vs last year. I have created a formula to give me yesterdays date with one cell showing the month, one the day and one the year (located in cell C1, D1 and E1 respectively) here are the formulas: =TEXT(TODAY()-1,"MMMM")...
  14. James_Latimer

    #Div/0! front end but not in VBA

    Hello, I have an issue that i cant get my head around. I have a formula on a sheet: =AD504/AD503 which returns #Div/0! However if i use the following vba i get an answer... Sub hjklasdfasdfad() Dim V1 As Double, V2 As Double V1 = ActiveSheet.Range("AD503").Value V2 =...
  15. S

    Type Mismatch

    I am receiving a type mismatch on the line below and am not sure how to make the correction. If .Cells(E, 30 + D).value = "#DIV/0!" Then The target cell at the moment of the error has "#DIV/0!" as its contents. The line exists within a with statement: With wkbk.Sheets("Ratios")...
  16. S

    Average if Error

    Hello, Why am i getting #DIV/0! ??? =AVERAGEIFS($C$3:$C$40929,$B$3:$B$40929,$E3,$D$3:$D$40929,F$2) <colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody> 1 A B C D E F G H I J K L 2 Monday Tuesday Wednesday Thursday Friday Saturday Sunday Date...
  17. S

    Average if with TIME

    Hello, While using the following formula i receive "#DIV0!" for the time stamp of 8:00 AM, what can i do to get the proper average in the column AA format =AVERAGEIFS($AA$3:$AA$40929,$Z$3:$Z$40929,$AC3,$AB$3:$AB$40929,AD$2) <tbody>...
  18. V

    #Div/0 won't dissapear

    Hey guys and/or girls! So I started making some more complex use of excel and I have come to an little problem. If I do: =AVERAGEIF(E26:J26, "<>0")+K26 Info: - E26:J26 can be a number but also N/A atm - K26, same as above but I keep getting those #Div/0 warnings, is there anyway to get rid...
  19. H

    finding the last 3 months in a table

    Hello, I have a table with months I and various figures for each month. I am wanting to display in columns O the last 3 months that have data in column D (completed). For Example below August, September and October should have a 1,2,3 in column O This is what I have but it won't work...
  20. J

    #DIV/0! Error when calculating averages over time

    I am using the below average if formula to determine average employee performance rating. I am trying to get rid of the #DIV/0! from cells where data has yet to be populated, as I want to set this up to be used in the following years and indicate an overall average. =AVERAGEIF(B4:E4,"<>0")...

Some videos you may like

This Week's Hot Topics