Finding Percentages Between Two Cells and Ignoring Empty Rows

MDean3313

New Member
Joined
Jul 8, 2019
Messages
13
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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),"")
 
Upvote 0
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!
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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,""),"")
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top