sumif to divide cell A1 by A2 unless A2>A1

  • Thread starter Thread starter jmkm
  • Start date Start date
J

jmkm

I am trying to return percentages by dividing 2 cells except if one cel
is greater than the other. Then I want to divide backwards. Problem i
if one celll is less than the other I get a sum greater than 100%.
have tried several variations of the formula but keep getting errors
Any help is greatly appreciated
 
Thanks a million. I actually found another that did work, but this on
also works. Here's the one I found
=SUM(IF(C4>B5,SUM(B5/C4),SUM(C4/B5))). I have another if you don'
mind, I am trying to average the calculated percentages by an entir
column. When there is nothing to divide, you obviously get the #DIV/0
error. I want to ignore those until values are entered. The formula
are to be entered in L2, L3, L4. L2 averages column G, L3 average
column H and L4 averages column I. I attached the file if this helps
Any suggestions? Thanks again. Joh

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=50032
 
First of all, the "SUMs" in the formula that you found are all
*unnecessary*.
Use John's formula instead.

Second, I didn't open your attachment (attachments are frowned upon in these
groups), but try this to eliminate the #DIV/0! errors:

=AVERAGE(IF(NOT(ISERR(G11:G20)),G11:G20))

NOTE! - this is an array formula and *must* be entered with CSE (<Ctrl>
<Shift> <Enter>).
If done correctly, the formula will *automatically* be enclosed in curly
brackets.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thanks a million. I actually found another that did work, but this one
also works. Here's the one I found,
=SUM(IF(C4>B5,SUM(B5/C4),SUM(C4/B5))). I have another if you don't
mind, I am trying to average the calculated percentages by an entire
column. When there is nothing to divide, you obviously get the #DIV/0!
error. I want to ignore those until values are entered. The formulas
are to be entered in L2, L3, L4. L2 averages column G, L3 averages
column H and L4 averages column I. I attached the file if this helps.
Any suggestions? Thanks again. John

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=500322
 
Back
Top