# IF with MAX and an ABS in a formula I am trying to decipher. help me to understand it.

#### blbat

##### Active Member
Trying to fathom a formula I inherited from someone else-

=IF(F7=0,"",MAX((F7-ABS(K7/3*12))/F7,0))

I would like to know if it is flat WRONG mathmatically, or the formula is wrong for the intended purpose.

I know his intent was to calculate the percentage of the number left at the end of the 1st Quarter of the year from the beginning of the year.

looks like this:

Excel 2010
BCDEFGHIJKLM
4NumberNumber toNum possibleNumbers to subtract by Month:ANNUALIZED
5Beg of QtrDisregardEnd of QTRMonth # 1Month # 2Month # 3TOTALNumber Kept:
6
764163250755.56%

<tbody>
</tbody>
1st Qtr 2014

Worksheet Formulas
CellFormula
F7=IF(COUNT(D7)=0,B7,B7-ABS(D7))
K7=SUM(H7:J7)
M7=IF(F7=0,"",MAX((F7-ABS(K7/3*12))/F7,0))

<tbody>
</tbody>

<tbody>
</tbody>

I Believe it should be this:

Excel 2010
DEFGHI
10Num possible End of QTRNum to subtract TotalNumber Kept% Kept
116375688.89%

<tbody>
</tbody>
1st Qtr 2014

Worksheet Formulas
CellFormula
D11=IF(COUNT(D7)=0,B7,B7-ABS(D7))
H11=(D11-F11)
I11=(H11/D11)*1

<tbody>
</tbody>

<tbody>
</tbody>

any input would be greatly appreciated!

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi, just curious why count? and why *1?

I think the count was maybe his way of avoiding an error if the cell didn't have a number in it?

I have no idea what you are trying to accomplish. Your formulas are all out of whack. Even the ones you made. There is no reason to use COUNT or ABS in your formula. Then there is that if statement that makes sure that F7 can never be 0. Yet there is another if statement that asks if F7 is 0. Seriously? Are we for real right now? I think the best way to get answers is to start all over. Post a dataset and tell us in sentenses what you want to accomplish and in which cells you would like formulas to go. For example: I want F7 to subtract B7 from D7. I can't even guess what is going on in that mess.

for those of you with civilized responses- Thanks.

for those of you with less-than-civilized responses- if you had read the original post you would see that the formula was not mine, that I inherited it, AND IT DOES NOT MAKE SENSE to me either.

So I am trying to de-construct the thought process of the original.

the example formula I put in at the bottom was simply to illustrate math behind percentage comparison of leftover number to original number.

(What percentage is 56 of 63? =%88.89)

if any of you day-time top guns would like to help out, I would appreciate it!

for those of you with civilized responses- Thanks.
=IF(ISBLANK(D7),B7,B7-ABS(D7)) could be used instead of the COUNT IF D7 would indeed possibly hold a negative integer.
if not then =IF(ISBLANK(D7),B7,B7-D7) should be enough.

if any of you day-time top guns would like to help out, I would appreciate it!
Not sure if I qualify as such but =(D11-F11) would suffice in H11 and =H11/D11 in I11 formatted as percentage, You do not need the *1.

cyrilbrd-

I don't believe D7 could be negative in the original formula...so your solutions would certainly work.

(What's being measured here is Live accounts. )

My problem is that I truly can't understand what the original author was trying to accomplish with his formula- in particular I don't understand the portion with the Absolute Value arguement:

=IF(F7=0,"",MAX((F7-ABS(K7/3*12))/F7,0))

So here's my attempt at putting that weirdness into English:

IF F7 equals zero, then Zero, Else
Give me the the MAX ~Largest Value~ of F7 minus the absolute value of K7 divided by three times Twelve, divided by F7, or Zero.

it just doesn't make sense.

cyrilbrd-

I don't believe D7 could be negative in the original formula...so your solutions would certainly work.

(What's being measured here is Live accounts. )

My problem is that I truly can't understand what the original author was trying to accomplish with his formula- in particular I don't understand the portion with the Absolute Value arguement:

=IF(F7=0,"",MAX((F7-ABS(K7/3*12))/F7,0))

So here's my attempt at putting that weirdness into English:

IF F7 equals zero, then Zero, Else
Give me the the MAX ~Largest Value~ of F7 minus the absolute value of K7 divided by three times Twelve, divided by F7, or Zero.

it just doesn't make sense.
Yes indeed... ABS Returns the absolute value of a number. The absolute value of a number is the number without its sign.
It would be interesting to ask the owner what he/she intended to achieve...

Replies
18
Views
801
Replies
4
Views
190
Replies
19
Views
1K
Replies
0
Views
1K
Replies
3
Views
420

1,221,215
Messages
6,158,580
Members
451,501
Latest member
andysacko

### 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.

### Which adblocker are you using?

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

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