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

[blbat]

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

	B	C	D	E	F	G	H	I	J	K	L	M
4	Number		Number to		Num possible		Numbers to subtract by Month:					ANNUALIZED
5	Beg of Qtr		Disregard		End of QTR		Month # 1	Month # 2	Month # 3	TOTAL		Number Kept:
6
7	64		1		63		2	5	0	7		55.56%

<tbody>
</tbody>

1st Qtr 2014​

Worksheet Formulas Cell Formula 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

	D	E	F	G	H	I
10	Num possible End of QTR		Num to subtract Total		Number Kept	% Kept
11	63		7		56	88.89%

<tbody>
</tbody>

1st Qtr 2014​

Worksheet Formulas Cell Formula 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!

 

[cyrilbrd]

Hi, just curious why count? and why *1?

 

[ztodd]

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

 

[WarPigl3t]

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]

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]

for those of you with civilized responses- Thanks.

Most welcome, please kindly advise if D7 could be negative.
=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]

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]

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