# 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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### cyrilbrd

##### Well-known Member
Hi, just curious why count? and why *1?

#### ztodd

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

#### WarPigl3t

##### Well-known Member
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.

#### blbat

##### Active Member
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!

#### cyrilbrd

##### Well-known Member
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.

#### blbat

##### Active Member
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

##### Well-known Member
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
0
Views
327
Replies
6
Views
358
Replies
1
Views
572
Replies
6
Views
339
Replies
2
Views
399

### Forum statistics

1,190,743
Messages
5,982,703
Members
439,790
Latest member
jonaust ### 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