Finding Percentages Between Two Cells and Ignoring Empty Rows

[MDean3313]

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 don't have any numerical values/are blank, I get a "#DIV/0!" when I want it to be blank.
I know it's a simple fix, I just can't seem to figure out what is going wrong.

Thanks for all the help!

 

[tyija1995]

Hey, the average between two cells can be given by the following (with nested IFERROR to capture errors and set them to blank as requested):

=IFERROR(SUM(AT2,AU2)/COUNT(AT2,AU2),"")

 

[MDean3313]

Thank you very much.

However, I didn't mean average, as you can see by my title, I meant to say find the percent difference between the two. For example, 175,156/247,778 should equal 54.7%.

Thanks!

 

[tyija1995]

Righto gotcha!

Then yeah, dividing the two should give the result - not sure why you get 1 when you try 100 and 4562346 though...

Nest the IFERROR to provide a blank where applicable!

=IFERROR(AT2/AU2,"")

Should work...

 

[Aryatect]

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 don't have any numerical values/are blank, I get a "#DIV/0!" when I want it to be blank.
I know it's a simple fix, I just can't seem to figure out what is going wrong.

Thanks for all the help!

Can you please post some dummy data and expected outcome in excel format, it will help in our understanding.

 

[MDean3313]

Thank you tyija1995, your last post worked!

As for your reply Aryatect, and future reference to anyone with a similair issue, here is some dummy data (not sure how to do excel table formatting here, apologies):

AT AU AV
1 Label Label Label
2 32,986 32,986 1.000
3
4
5 175,156 320,400 0.547

The formula =IFERROR(AT2/AU2,"") was successful in comparing two values, giving a percentage out, and leaving rows 3&4 result blank, as the cells are blank.
The reason I was only getting a "1" for each result that compared numbers.... was because I needed to simply change the number section from "Custom" to "Number."

If possible, could someone explain why the IFERROR statement worked and not just an IF statement? Is it because there were empty cells trying to use the IF statement?

Thank you again for all the help with this.

 

[tyija1995]

Hey,

glad it worked!

IFERROR returns one of two results, a result where no errors occur (value) OR another result where an error occurs (value_if_error) - so you can specify the output if there is an error, otherwise return the division of the 2 cells.

IF won't do much in this case as you don't really have a criteria, you're just dividing two cells, you'll either get a value, or an error depending on the cells, hence IFERROR is the ideal function.

An IF statement could be used if you wanted to divide the two cells where a certain criteria is first obeyed, perhaps you want AT2 and AU2 to both be positive, you could write something like:

=IF(AND(AT2 > 0, AU2 > 0),IFERROR(AT2/AU2,""),"")