What's wrong with this IF function.
 

=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"†�","")

What's wrong with this IF function.
 

Hi,

It's not entirely clear what your trying to do but maybe this

=IF(RIGHT(B1,6)+0RIGHT(B7,6)+0,"†�","")

Mike

"GEM" wrote:

=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"†�","")

What's wrong with this IF function.
 

In B1 through B7, I have two numbers in the same cell, something like this
B1=$20.44 - 13.00%. I used the "&" to have the two numbers together in the
same cell.

I want to show an arrow on column C where the percentage is the highest. The
results on column B change everyday because I am using the =TODAY().

So how do I get to show an arrow where the percentage is highest?? Something
close to my IF function??

=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"†�","")




"Mike H" wrote:

Hi,

It's not entirely clear what your trying to do but maybe this

=IF(RIGHT(B1,6)+0RIGHT(B7,6)+0,"†�","")

Mike

"GEM" wrote:

=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"†�","")

What's wrong with this IF function.
 

What formula do you have in B1 to display those values?

Are they *legal* XL numbers that can be calculated ... or might they be
text?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"GEM" wrote in message
...
In B1 through B7, I have two numbers in the same cell, something like this
B1=$20.44 - 13.00%. I used the "&" to have the two numbers together in the
same cell.

I want to show an arrow on column C where the percentage is the highest.
The
results on column B change everyday because I am using the =TODAY().

So how do I get to show an arrow where the percentage is highest??
Something
close to my IF function??

=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"?","")




"Mike H" wrote:

Hi,

It's not entirely clear what your trying to do but maybe this

=IF(RIGHT(B1,6)+0RIGHT(B7,6)+0,"?","")

Mike

"GEM" wrote:

=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"?","")

What's wrong with this IF function.
 

The formula I have is this,

=TEXT((SUMIF(H20:H65536,TODAY()-WEEKDAY(NOW(),3)+{5},I20:I65536)+SUMIF(H20:H65536, TODAY()-WEEKDAY(NOW(),3)+{5},K20:K1048576)),"$
#,##0.00")&" -
"&TEXT((SUMIF(H20:H65536,TODAY()-WEEKDAY(NOW(),3)+{5},I20:I65536)+SUMIF(H20:H65536, TODAY()-WEEKDAY(NOW(),3)+{5},K20:K1048576))/$C$9,"0.00%")

How can I work with the numbers in this cell??

"RagDyer" wrote:

What formula do you have in B1 to display those values?

Are they *legal* XL numbers that can be calculated ... or might they be
text?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"GEM" wrote in message
...
In B1 through B7, I have two numbers in the same cell, something like this
B1=$20.44 - 13.00%. I used the "&" to have the two numbers together in the
same cell.

I want to show an arrow on column C where the percentage is the highest.
The
results on column B change everyday because I am using the =TODAY().

So how do I get to show an arrow where the percentage is highest??
Something
close to my IF function??

=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"?","")




"Mike H" wrote:

Hi,

It's not entirely clear what your trying to do but maybe this

=IF(RIGHT(B1,6)+0RIGHT(B7,6)+0,"?","")

Mike

"GEM" wrote:

=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"?","")

What's wrong with this IF function.
 

If you would like to make that arrow *more prominent*, you might try
formatting C1 to the
WingDings font, and use this *array* formula in C1:

=IF(--RIGHT(B1,6)=MAX(--RIGHT($B$1:$B$7,6)),CHAR(239),"")

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down to C7.

*TAKE NOTE*

For this formula to work, your percentages must *not* exceed 99.99% (6
characters).

Changing the formula to 7 characters would bring the "dash" into the
calculations as a minus sign when you have single digit percentages.


*BTW*

You have a typo in your Column K references in the formula you posted!

Second - Do you really need so many rows for your calcs?

The formula could be made a little shorter using Sumproduct(), but *not* at
those ranges you're now using.

If you could live with a much smaller range, you might try something like
this:

=TEXT(SUMPRODUCT((H20:H2000=TODAY()-WEEKDAY(TODAY(),3)+5)*(I20:I2000+K20:K2000)),"$
#,##0.00")&" -
"&TEXT(SUMPRODUCT((H20:H2000=TODAY()-WEEKDAY(TODAY(),3)+5)*(I20:I2000+K20:K2000))/$C$9,"0.00%")


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"GEM" wrote in message
...
The formula I have is this,

=TEXT((SUMIF(H20:H65536,TODAY()-WEEKDAY(NOW(),3)+{5},I20:I65536)+SUMIF(H20:H65536, TODAY()-WEEKDAY(NOW(),3)+{5},K20:K1048576)),"$
#,##0.00")&" -
"&TEXT((SUMIF(H20:H65536,TODAY()-WEEKDAY(NOW(),3)+{5},I20:I65536)+SUMIF(H20:H65536, TODAY()-WEEKDAY(NOW(),3)+{5},K20:K1048576))/$C$9,"0.00%")

How can I work with the numbers in this cell??

"RagDyer" wrote:

What formula do you have in B1 to display those values?

Are they *legal* XL numbers that can be calculated ... or might they be
text?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"GEM" wrote in message
...
In B1 through B7, I have two numbers in the same cell, something like
this
B1=$20.44 - 13.00%. I used the "&" to have the two numbers together in
the
same cell.

I want to show an arrow on column C where the percentage is the
highest.
The
results on column B change everyday because I am using the =TODAY().

So how do I get to show an arrow where the percentage is highest??
Something
close to my IF function??

=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"?","")




"Mike H" wrote:

Hi,

It's not entirely clear what your trying to do but maybe this

=IF(RIGHT(B1,6)+0RIGHT(B7,6)+0,"?","")

Mike

"GEM" wrote:

=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"?","")