Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's wrong with this IF function.
=IF(B1=MAX(RIGHT(B1,6):RIGHT(B7,6)),"","")
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)),"","") |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)),"","") |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)),"?","") |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)),"?","") |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)),"?","") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is Wrong with this function?????????? | Excel Worksheet Functions | |||
Row() function returns wrong row and more.. | Excel Worksheet Functions | |||
What am I doing wrong with PMT function? | Excel Discussion (Misc queries) | |||
INDIRECT Function - what am I doing wrong? | Excel Discussion (Misc queries) | |||
something wrong with my "countif" function | Excel Discussion (Misc queries) |