Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
060628 Embedding IF: depends on result..
Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
Can you use multiple cells?
For instance, put your equation in A1 and then in B1: =a1*If(a11,10,100) nastech wrote: 060628 Embedding IF: depends on result.. Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
hi! thanks for the response, sorry for long time back.. been busy.. have
been tearing sheet formula's apart to reduce size of sheet. much work but went form 11MB to 9MB for same output of formulas. embedding was some of it, e.g.: instead of =if(a1="x",hyperlink($a$2&v9),"1",if(a1="y",hyperl ink($a$3&v9),"2")) to =hyperlink(if(a1="x",$a$2,$a$3)&v9),$a$1) not just hyperlinks.. anyways have had some grief over excel formats, custom vs formula. for 1 column version of % results (i.e. 90, see a 9) used to use 2 columns, worker column with 1 eq, e.g.: =IF(OR(BJ9={"",0},BJ9<0.02),"",((CB9-BZ9)/((CA9-0.0001)-BZ9)*10)) instead of *100 for a normal 90 result, see e.g.: 9 small problem is now 13 (130%) is what is seen. off worker column above had series of 10 formulas: if(90-.5)/10,"9" for each level of 10, made equation 10x in size. doing so much back-forth, question no longer seems to matter. (used to look like): =IF(OR(BN9="",BN9<0.02),"",IF(AI9<=$BD$7,IF($BD$7< 10,$BD$7/1,$BD$7/10), IF(OR(AI9=$BD$2,AND(AI9<(INDIRECT($BD$18)-0.5),AI9INDIRECT($BD$19))),(CE9-CC9)/((CD9-0.0001)-CC9)*$BV$19, IF(AI9<=$BD$6,$BD$6/10, IF(AI9<=$BD$5,$BD$5/10, IF(AI9<=$BD$4,$BD$4/10, IF(AI9<=$BD$3,$BD$3/10, IF(AI9<=$BD$2,$BD$2/10)))))))) problems: - excel formatting divide by thousands only with comma's? ,, needed divide by 10 or 100, could not find to save life, must be a way. just not in excel help for an example. - no setting for do not round??? had to use alot of if(xy,(z-0.5), til figured out to use less than < - still have problem with character/ text combo. examples a 12:18pm - <b0.63</b 12:18pm - <b10.63</b and pulling the number from that (0.63 or 10.63) quickest answer see is cut / move destination of that colum other than download "to" and somehow left/right len or istext/isnum?. thanks (sorry for the lack of info on previous.. ) "Dave Peterson" wrote: Can you use multiple cells? For instance, put your equation in A1 and then in B1: =a1*If(a11,10,100) nastech wrote: 060628 Embedding IF: depends on result.. Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
I'd use a helper cell to display in different powers of 10.
Format the cell as Number with the correct number of digits after the decimal point and the displayed value won't round if you shrink the columnwidth--but you may see ###'s if you shrink the columnwidth too much. Do you really embed the <b and </b or is that just in the post. If you embed them in the cell, you can pick out the stuff by using something like: =MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) if you want the number (not text) =--MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) If you have 12:18pm - 10.63 in the cell, you could search for the hypen and extra everything to the right. =TRIM(MID(A1,SEARCH("-",A1)+1,255)) or =--(MID(A1,SEARCH("-",A1)+1,255)) nastech wrote: hi! thanks for the response, sorry for long time back.. been busy.. have been tearing sheet formula's apart to reduce size of sheet. much work but went form 11MB to 9MB for same output of formulas. embedding was some of it, e.g.: instead of =if(a1="x",hyperlink($a$2&v9),"1",if(a1="y",hyperl ink($a$3&v9),"2")) to =hyperlink(if(a1="x",$a$2,$a$3)&v9),$a$1) not just hyperlinks.. anyways have had some grief over excel formats, custom vs formula. for 1 column version of % results (i.e. 90, see a 9) used to use 2 columns, worker column with 1 eq, e.g.: =IF(OR(BJ9={"",0},BJ9<0.02),"",((CB9-BZ9)/((CA9-0.0001)-BZ9)*10)) instead of *100 for a normal 90 result, see e.g.: 9 small problem is now 13 (130%) is what is seen. off worker column above had series of 10 formulas: if(90-.5)/10,"9" for each level of 10, made equation 10x in size. doing so much back-forth, question no longer seems to matter. (used to look like): =IF(OR(BN9="",BN9<0.02),"",IF(AI9<=$BD$7,IF($BD$7< 10,$BD$7/1,$BD$7/10), IF(OR(AI9=$BD$2,AND(AI9<(INDIRECT($BD$18)-0.5),AI9INDIRECT($BD$19))),(CE9-CC9)/((CD9-0.0001)-CC9)*$BV$19, IF(AI9<=$BD$6,$BD$6/10, IF(AI9<=$BD$5,$BD$5/10, IF(AI9<=$BD$4,$BD$4/10, IF(AI9<=$BD$3,$BD$3/10, IF(AI9<=$BD$2,$BD$2/10)))))))) problems: - excel formatting divide by thousands only with comma's? ,, needed divide by 10 or 100, could not find to save life, must be a way. just not in excel help for an example. - no setting for do not round??? had to use alot of if(xy,(z-0.5), til figured out to use less than < - still have problem with character/ text combo. examples a 12:18pm - <b0.63</b 12:18pm - <b10.63</b and pulling the number from that (0.63 or 10.63) quickest answer see is cut / move destination of that colum other than download "to" and somehow left/right len or istext/isnum?. thanks (sorry for the lack of info on previous.. ) "Dave Peterson" wrote: Can you use multiple cells? For instance, put your equation in A1 and then in B1: =a1*If(a11,10,100) nastech wrote: 060628 Embedding IF: depends on result.. Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
Thankyou! will give them a look. should give me a good start, instead of
spending a day trying to experiment.. - will try the helper cell, should be short enough (compared to that is what was doing, but with that long formula listed below was the summary cell) - been looking at formats closer, that is how I am getting the data, trying to unembed (?) / parse? not sure yet what procedure is for "parsing", got the idea. further looking at data variations, how received: <i11:19am</i - <b<i0.16</i</b 11:35am - <b0.5601</b 11:35am - <b10.56</b 11:35am - <b1111.5601</b might be looking at something to find all after OR("i","b"), to 2 decimal points; should be a good base reference. thanks XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX "Dave Peterson" wrote: I'd use a helper cell to display in different powers of 10. Format the cell as Number with the correct number of digits after the decimal point and the displayed value won't round if you shrink the columnwidth--but you may see ###'s if you shrink the columnwidth too much. Do you really embed the <b and </b or is that just in the post. If you embed them in the cell, you can pick out the stuff by using something like: =MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) if you want the number (not text) =--MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) If you have 12:18pm - 10.63 in the cell, you could search for the hypen and extra everything to the right. =TRIM(MID(A1,SEARCH("-",A1)+1,255)) or =--(MID(A1,SEARCH("-",A1)+1,255)) nastech wrote: hi! thanks for the response, sorry for long time back.. been busy.. have been tearing sheet formula's apart to reduce size of sheet. much work but went form 11MB to 9MB for same output of formulas. embedding was some of it, e.g.: instead of =if(a1="x",hyperlink($a$2&v9),"1",if(a1="y",hyperl ink($a$3&v9),"2")) to =hyperlink(if(a1="x",$a$2,$a$3)&v9),$a$1) not just hyperlinks.. anyways have had some grief over excel formats, custom vs formula. for 1 column version of % results (i.e. 90, see a 9) used to use 2 columns, worker column with 1 eq, e.g.: =IF(OR(BJ9={"",0},BJ9<0.02),"",((CB9-BZ9)/((CA9-0.0001)-BZ9)*10)) instead of *100 for a normal 90 result, see e.g.: 9 small problem is now 13 (130%) is what is seen. off worker column above had series of 10 formulas: if(90-.5)/10,"9" for each level of 10, made equation 10x in size. doing so much back-forth, question no longer seems to matter. (used to look like): =IF(OR(BN9="",BN9<0.02),"",IF(AI9<=$BD$7,IF($BD$7< 10,$BD$7/1,$BD$7/10), IF(OR(AI9=$BD$2,AND(AI9<(INDIRECT($BD$18)-0.5),AI9INDIRECT($BD$19))),(CE9-CC9)/((CD9-0.0001)-CC9)*$BV$19, IF(AI9<=$BD$6,$BD$6/10, IF(AI9<=$BD$5,$BD$5/10, IF(AI9<=$BD$4,$BD$4/10, IF(AI9<=$BD$3,$BD$3/10, IF(AI9<=$BD$2,$BD$2/10)))))))) problems: - excel formatting divide by thousands only with comma's? ,, needed divide by 10 or 100, could not find to save life, must be a way. just not in excel help for an example. - no setting for do not round??? had to use alot of if(xy,(z-0.5), til figured out to use less than < - still have problem with character/ text combo. examples a 12:18pm - <b0.63</b 12:18pm - <b10.63</b and pulling the number from that (0.63 or 10.63) quickest answer see is cut / move destination of that colum other than download "to" and somehow left/right len or istext/isnum?. thanks (sorry for the lack of info on previous.. ) "Dave Peterson" wrote: Can you use multiple cells? For instance, put your equation in A1 and then in B1: =a1*If(a11,10,100) nastech wrote: 060628 Embedding IF: depends on result.. Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
p.s: # signs for narrowed column was what wanted / used. helps to reduce
view of numbers all 100, <0... same idea for reducing view to 10's with single digit, helps speed up visual scan of sheet. where my sheet took 8 to 10 times more space before, worker cell/ column made: if formual cell result 80, then 8; gave an absolute 8... second colum (3rd/4th column) with same idea could then be sorted as sub to (what mean is: saved much space, but 1st columns are absolute: 81, 82, or 8.1, 8.2.. see 8); means I can no longer sort the second set of columns. I believe this to be a Huge problem in excel: not having a check box, where I don't have to (80-0.5) on 1/2 the figures in every cell, thru-out the sheet. people otherwise don't know it, conditional format colors / "numbers viewed", are all off. In trying to quick visual scan a sheet: colors & numbers are all "OFF". Looking at 80 in 1 cell is green, the next it is red... Is there a way to turn off rounding up / down in a sheet? Number / Text Combination "Custom" formatting: what else would have done, is for having examples of custom formatting, numbers 100 <0 (see # signs) to be able to format x10 see 120.. stead of 12 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
ps: for text / numbers:
Number / Text Combination "Custom" formatting: - is there a way to include examples (if exist), for text / number combinations: removing text from? etc. ---------- Great result on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255)) do not think would have got that for a while. is there a way to "OR" the search( on "<i", trying some variations on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH(OR({"<b","<i"}),CC9)+3,255)) not quite working, will do the longer way if have to "Dave Peterson" wrote: I'd use a helper cell to display in different powers of 10. Format the cell as Number with the correct number of digits after the decimal point and the displayed value won't round if you shrink the columnwidth--but you may see ###'s if you shrink the columnwidth too much. Do you really embed the <b and </b or is that just in the post. If you embed them in the cell, you can pick out the stuff by using something like: =MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) if you want the number (not text) =--MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) If you have 12:18pm - 10.63 in the cell, you could search for the hypen and extra everything to the right. =TRIM(MID(A1,SEARCH("-",A1)+1,255)) or =--(MID(A1,SEARCH("-",A1)+1,255)) nastech wrote: hi! thanks for the response, sorry for long time back.. been busy.. have been tearing sheet formula's apart to reduce size of sheet. much work but went form 11MB to 9MB for same output of formulas. embedding was some of it, e.g.: instead of =if(a1="x",hyperlink($a$2&v9),"1",if(a1="y",hyperl ink($a$3&v9),"2")) to =hyperlink(if(a1="x",$a$2,$a$3)&v9),$a$1) not just hyperlinks.. anyways have had some grief over excel formats, custom vs formula. for 1 column version of % results (i.e. 90, see a 9) used to use 2 columns, worker column with 1 eq, e.g.: =IF(OR(BJ9={"",0},BJ9<0.02),"",((CB9-BZ9)/((CA9-0.0001)-BZ9)*10)) instead of *100 for a normal 90 result, see e.g.: 9 small problem is now 13 (130%) is what is seen. off worker column above had series of 10 formulas: if(90-.5)/10,"9" for each level of 10, made equation 10x in size. doing so much back-forth, question no longer seems to matter. (used to look like): =IF(OR(BN9="",BN9<0.02),"",IF(AI9<=$BD$7,IF($BD$7< 10,$BD$7/1,$BD$7/10), IF(OR(AI9=$BD$2,AND(AI9<(INDIRECT($BD$18)-0.5),AI9INDIRECT($BD$19))),(CE9-CC9)/((CD9-0.0001)-CC9)*$BV$19, IF(AI9<=$BD$6,$BD$6/10, IF(AI9<=$BD$5,$BD$5/10, IF(AI9<=$BD$4,$BD$4/10, IF(AI9<=$BD$3,$BD$3/10, IF(AI9<=$BD$2,$BD$2/10)))))))) problems: - excel formatting divide by thousands only with comma's? ,, needed divide by 10 or 100, could not find to save life, must be a way. just not in excel help for an example. - no setting for do not round??? had to use alot of if(xy,(z-0.5), til figured out to use less than < - still have problem with character/ text combo. examples a 12:18pm - <b0.63</b 12:18pm - <b10.63</b and pulling the number from that (0.63 or 10.63) quickest answer see is cut / move destination of that colum other than download "to" and somehow left/right len or istext/isnum?. thanks (sorry for the lack of info on previous.. ) "Dave Peterson" wrote: Can you use multiple cells? For instance, put your equation in A1 and then in B1: =a1*If(a11,10,100) nastech wrote: 060628 Embedding IF: depends on result.. Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
Maybe it would be better to remove the <i and <b and </i and </b from the
expression and then just look for the hyphen? Put this kind of formula in an adjacent cell: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"< i",""), "</i",""),"<b",""),"</b","") (All one cell) And then use that second suggestion to search for the hypen. But this technique will fail after a couple more HTML tags. You only get 7 nested functions. nastech wrote: ps: for text / numbers: Number / Text Combination "Custom" formatting: - is there a way to include examples (if exist), for text / number combinations: removing text from? etc. ---------- Great result on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255)) do not think would have got that for a while. is there a way to "OR" the search( on "<i", trying some variations on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH(OR({"<b","<i"}),CC9)+3,255)) not quite working, will do the longer way if have to "Dave Peterson" wrote: I'd use a helper cell to display in different powers of 10. Format the cell as Number with the correct number of digits after the decimal point and the displayed value won't round if you shrink the columnwidth--but you may see ###'s if you shrink the columnwidth too much. Do you really embed the <b and </b or is that just in the post. If you embed them in the cell, you can pick out the stuff by using something like: =MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) if you want the number (not text) =--MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) If you have 12:18pm - 10.63 in the cell, you could search for the hypen and extra everything to the right. =TRIM(MID(A1,SEARCH("-",A1)+1,255)) or =--(MID(A1,SEARCH("-",A1)+1,255)) nastech wrote: hi! thanks for the response, sorry for long time back.. been busy.. have been tearing sheet formula's apart to reduce size of sheet. much work but went form 11MB to 9MB for same output of formulas. embedding was some of it, e.g.: instead of =if(a1="x",hyperlink($a$2&v9),"1",if(a1="y",hyperl ink($a$3&v9),"2")) to =hyperlink(if(a1="x",$a$2,$a$3)&v9),$a$1) not just hyperlinks.. anyways have had some grief over excel formats, custom vs formula. for 1 column version of % results (i.e. 90, see a 9) used to use 2 columns, worker column with 1 eq, e.g.: =IF(OR(BJ9={"",0},BJ9<0.02),"",((CB9-BZ9)/((CA9-0.0001)-BZ9)*10)) instead of *100 for a normal 90 result, see e.g.: 9 small problem is now 13 (130%) is what is seen. off worker column above had series of 10 formulas: if(90-.5)/10,"9" for each level of 10, made equation 10x in size. doing so much back-forth, question no longer seems to matter. (used to look like): =IF(OR(BN9="",BN9<0.02),"",IF(AI9<=$BD$7,IF($BD$7< 10,$BD$7/1,$BD$7/10), IF(OR(AI9=$BD$2,AND(AI9<(INDIRECT($BD$18)-0.5),AI9INDIRECT($BD$19))),(CE9-CC9)/((CD9-0.0001)-CC9)*$BV$19, IF(AI9<=$BD$6,$BD$6/10, IF(AI9<=$BD$5,$BD$5/10, IF(AI9<=$BD$4,$BD$4/10, IF(AI9<=$BD$3,$BD$3/10, IF(AI9<=$BD$2,$BD$2/10)))))))) problems: - excel formatting divide by thousands only with comma's? ,, needed divide by 10 or 100, could not find to save life, must be a way. just not in excel help for an example. - no setting for do not round??? had to use alot of if(xy,(z-0.5), til figured out to use less than < - still have problem with character/ text combo. examples a 12:18pm - <b0.63</b 12:18pm - <b10.63</b and pulling the number from that (0.63 or 10.63) quickest answer see is cut / move destination of that colum other than download "to" and somehow left/right len or istext/isnum?. thanks (sorry for the lack of info on previous.. ) "Dave Peterson" wrote: Can you use multiple cells? For instance, put your equation in A1 and then in B1: =a1*If(a11,10,100) nastech wrote: 060628 Embedding IF: depends on result.. Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
You'll see what looks like rounding if the cell(s) are formatted as General and
you shrink the columns. But the underlying value doesn't change. If you have to shrink the columnwidth, how about shrinking the font size, too. Format|Cells|alignment tab|check the "shrink to fit" box nastech wrote: p.s: # signs for narrowed column was what wanted / used. helps to reduce view of numbers all 100, <0... same idea for reducing view to 10's with single digit, helps speed up visual scan of sheet. where my sheet took 8 to 10 times more space before, worker cell/ column made: if formual cell result 80, then 8; gave an absolute 8... second colum (3rd/4th column) with same idea could then be sorted as sub to (what mean is: saved much space, but 1st columns are absolute: 81, 82, or 8.1, 8.2.. see 8); means I can no longer sort the second set of columns. I believe this to be a Huge problem in excel: not having a check box, where I don't have to (80-0.5) on 1/2 the figures in every cell, thru-out the sheet. people otherwise don't know it, conditional format colors / "numbers viewed", are all off. In trying to quick visual scan a sheet: colors & numbers are all "OFF". Looking at 80 in 1 cell is green, the next it is red... Is there a way to turn off rounding up / down in a sheet? Number / Text Combination "Custom" formatting: what else would have done, is for having examples of custom formatting, numbers 100 <0 (see # signs) to be able to format x10 see 120.. stead of 12 -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
you beat me back.. was trying
=IF(CC9="","",IF(LEFT(CC9,3)<"<i",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255), --MID(LEFT(CC9,LEN(CC9)-8),SEARCH("<b<i",CC9)+3,255))) for 11:35am - <b0.5601</b <i11:19am</i - <b<i0.16</i</b first half works, not the second... don't get that "Dave Peterson" wrote: Maybe it would be better to remove the <i and <b and </i and </b from the expression and then just look for the hyphen? Put this kind of formula in an adjacent cell: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"< i",""), "</i",""),"<b",""),"</b","") (All one cell) And then use that second suggestion to search for the hypen. But this technique will fail after a couple more HTML tags. You only get 7 nested functions. nastech wrote: ps: for text / numbers: Number / Text Combination "Custom" formatting: - is there a way to include examples (if exist), for text / number combinations: removing text from? etc. ---------- Great result on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255)) do not think would have got that for a while. is there a way to "OR" the search( on "<i", trying some variations on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH(OR({"<b","<i"}),CC9)+3,255)) not quite working, will do the longer way if have to "Dave Peterson" wrote: I'd use a helper cell to display in different powers of 10. Format the cell as Number with the correct number of digits after the decimal point and the displayed value won't round if you shrink the columnwidth--but you may see ###'s if you shrink the columnwidth too much. Do you really embed the <b and </b or is that just in the post. If you embed them in the cell, you can pick out the stuff by using something like: =MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) if you want the number (not text) =--MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) If you have 12:18pm - 10.63 in the cell, you could search for the hypen and extra everything to the right. =TRIM(MID(A1,SEARCH("-",A1)+1,255)) or =--(MID(A1,SEARCH("-",A1)+1,255)) nastech wrote: hi! thanks for the response, sorry for long time back.. been busy.. have been tearing sheet formula's apart to reduce size of sheet. much work but went form 11MB to 9MB for same output of formulas. embedding was some of it, e.g.: instead of =if(a1="x",hyperlink($a$2&v9),"1",if(a1="y",hyperl ink($a$3&v9),"2")) to =hyperlink(if(a1="x",$a$2,$a$3)&v9),$a$1) not just hyperlinks.. anyways have had some grief over excel formats, custom vs formula. for 1 column version of % results (i.e. 90, see a 9) used to use 2 columns, worker column with 1 eq, e.g.: =IF(OR(BJ9={"",0},BJ9<0.02),"",((CB9-BZ9)/((CA9-0.0001)-BZ9)*10)) instead of *100 for a normal 90 result, see e.g.: 9 small problem is now 13 (130%) is what is seen. off worker column above had series of 10 formulas: if(90-.5)/10,"9" for each level of 10, made equation 10x in size. doing so much back-forth, question no longer seems to matter. (used to look like): =IF(OR(BN9="",BN9<0.02),"",IF(AI9<=$BD$7,IF($BD$7< 10,$BD$7/1,$BD$7/10), IF(OR(AI9=$BD$2,AND(AI9<(INDIRECT($BD$18)-0.5),AI9INDIRECT($BD$19))),(CE9-CC9)/((CD9-0.0001)-CC9)*$BV$19, IF(AI9<=$BD$6,$BD$6/10, IF(AI9<=$BD$5,$BD$5/10, IF(AI9<=$BD$4,$BD$4/10, IF(AI9<=$BD$3,$BD$3/10, IF(AI9<=$BD$2,$BD$2/10)))))))) problems: - excel formatting divide by thousands only with comma's? ,, needed divide by 10 or 100, could not find to save life, must be a way. just not in excel help for an example. - no setting for do not round??? had to use alot of if(xy,(z-0.5), til figured out to use less than < - still have problem with character/ text combo. examples a 12:18pm - <b0.63</b 12:18pm - <b10.63</b and pulling the number from that (0.63 or 10.63) quickest answer see is cut / move destination of that colum other than download "to" and somehow left/right len or istext/isnum?. thanks (sorry for the lack of info on previous.. ) "Dave Peterson" wrote: Can you use multiple cells? For instance, put your equation in A1 and then in B1: =a1*If(a11,10,100) nastech wrote: 060628 Embedding IF: depends on result.. Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
I would use that helper cell and elimate that HTML junk before I tried to
extract the number. nastech wrote: you beat me back.. was trying =IF(CC9="","",IF(LEFT(CC9,3)<"<i",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255), --MID(LEFT(CC9,LEN(CC9)-8),SEARCH("<b<i",CC9)+3,255))) for 11:35am - <b0.5601</b <i11:19am</i - <b<i0.16</i</b first half works, not the second... don't get that "Dave Peterson" wrote: Maybe it would be better to remove the <i and <b and </i and </b from the expression and then just look for the hyphen? Put this kind of formula in an adjacent cell: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"< i",""), "</i",""),"<b",""),"</b","") (All one cell) And then use that second suggestion to search for the hypen. But this technique will fail after a couple more HTML tags. You only get 7 nested functions. nastech wrote: ps: for text / numbers: Number / Text Combination "Custom" formatting: - is there a way to include examples (if exist), for text / number combinations: removing text from? etc. ---------- Great result on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255)) do not think would have got that for a while. is there a way to "OR" the search( on "<i", trying some variations on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH(OR({"<b","<i"}),CC9)+3,255)) not quite working, will do the longer way if have to "Dave Peterson" wrote: I'd use a helper cell to display in different powers of 10. Format the cell as Number with the correct number of digits after the decimal point and the displayed value won't round if you shrink the columnwidth--but you may see ###'s if you shrink the columnwidth too much. Do you really embed the <b and </b or is that just in the post. If you embed them in the cell, you can pick out the stuff by using something like: =MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) if you want the number (not text) =--MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) If you have 12:18pm - 10.63 in the cell, you could search for the hypen and extra everything to the right. =TRIM(MID(A1,SEARCH("-",A1)+1,255)) or =--(MID(A1,SEARCH("-",A1)+1,255)) nastech wrote: hi! thanks for the response, sorry for long time back.. been busy.. have been tearing sheet formula's apart to reduce size of sheet. much work but went form 11MB to 9MB for same output of formulas. embedding was some of it, e.g.: instead of =if(a1="x",hyperlink($a$2&v9),"1",if(a1="y",hyperl ink($a$3&v9),"2")) to =hyperlink(if(a1="x",$a$2,$a$3)&v9),$a$1) not just hyperlinks.. anyways have had some grief over excel formats, custom vs formula. for 1 column version of % results (i.e. 90, see a 9) used to use 2 columns, worker column with 1 eq, e.g.: =IF(OR(BJ9={"",0},BJ9<0.02),"",((CB9-BZ9)/((CA9-0.0001)-BZ9)*10)) instead of *100 for a normal 90 result, see e.g.: 9 small problem is now 13 (130%) is what is seen. off worker column above had series of 10 formulas: if(90-.5)/10,"9" for each level of 10, made equation 10x in size. doing so much back-forth, question no longer seems to matter. (used to look like): =IF(OR(BN9="",BN9<0.02),"",IF(AI9<=$BD$7,IF($BD$7< 10,$BD$7/1,$BD$7/10), IF(OR(AI9=$BD$2,AND(AI9<(INDIRECT($BD$18)-0.5),AI9INDIRECT($BD$19))),(CE9-CC9)/((CD9-0.0001)-CC9)*$BV$19, IF(AI9<=$BD$6,$BD$6/10, IF(AI9<=$BD$5,$BD$5/10, IF(AI9<=$BD$4,$BD$4/10, IF(AI9<=$BD$3,$BD$3/10, IF(AI9<=$BD$2,$BD$2/10)))))))) problems: - excel formatting divide by thousands only with comma's? ,, needed divide by 10 or 100, could not find to save life, must be a way. just not in excel help for an example. - no setting for do not round??? had to use alot of if(xy,(z-0.5), til figured out to use less than < - still have problem with character/ text combo. examples a 12:18pm - <b0.63</b 12:18pm - <b10.63</b and pulling the number from that (0.63 or 10.63) quickest answer see is cut / move destination of that colum other than download "to" and somehow left/right len or istext/isnum?. thanks (sorry for the lack of info on previous.. ) "Dave Peterson" wrote: Can you use multiple cells? For instance, put your equation in A1 and then in B1: =a1*If(a11,10,100) nastech wrote: 060628 Embedding IF: depends on result.. Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
must be something minor, do not see why #value error.
"Dave Peterson" wrote: Maybe it would be better to remove the <i and <b and </i and </b from the expression and then just look for the hyphen? Put this kind of formula in an adjacent cell: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"< i",""), "</i",""),"<b",""),"</b","") (All one cell) And then use that second suggestion to search for the hypen. But this technique will fail after a couple more HTML tags. You only get 7 nested functions. nastech wrote: ps: for text / numbers: Number / Text Combination "Custom" formatting: - is there a way to include examples (if exist), for text / number combinations: removing text from? etc. ---------- Great result on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255)) do not think would have got that for a while. is there a way to "OR" the search( on "<i", trying some variations on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH(OR({"<b","<i"}),CC9)+3,255)) not quite working, will do the longer way if have to "Dave Peterson" wrote: I'd use a helper cell to display in different powers of 10. Format the cell as Number with the correct number of digits after the decimal point and the displayed value won't round if you shrink the columnwidth--but you may see ###'s if you shrink the columnwidth too much. Do you really embed the <b and </b or is that just in the post. If you embed them in the cell, you can pick out the stuff by using something like: =MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) if you want the number (not text) =--MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) If you have 12:18pm - 10.63 in the cell, you could search for the hypen and extra everything to the right. =TRIM(MID(A1,SEARCH("-",A1)+1,255)) or =--(MID(A1,SEARCH("-",A1)+1,255)) nastech wrote: hi! thanks for the response, sorry for long time back.. been busy.. have been tearing sheet formula's apart to reduce size of sheet. much work but went form 11MB to 9MB for same output of formulas. embedding was some of it, e.g.: instead of =if(a1="x",hyperlink($a$2&v9),"1",if(a1="y",hyperl ink($a$3&v9),"2")) to =hyperlink(if(a1="x",$a$2,$a$3)&v9),$a$1) not just hyperlinks.. anyways have had some grief over excel formats, custom vs formula. for 1 column version of % results (i.e. 90, see a 9) used to use 2 columns, worker column with 1 eq, e.g.: =IF(OR(BJ9={"",0},BJ9<0.02),"",((CB9-BZ9)/((CA9-0.0001)-BZ9)*10)) instead of *100 for a normal 90 result, see e.g.: 9 small problem is now 13 (130%) is what is seen. off worker column above had series of 10 formulas: if(90-.5)/10,"9" for each level of 10, made equation 10x in size. doing so much back-forth, question no longer seems to matter. (used to look like): =IF(OR(BN9="",BN9<0.02),"",IF(AI9<=$BD$7,IF($BD$7< 10,$BD$7/1,$BD$7/10), IF(OR(AI9=$BD$2,AND(AI9<(INDIRECT($BD$18)-0.5),AI9INDIRECT($BD$19))),(CE9-CC9)/((CD9-0.0001)-CC9)*$BV$19, IF(AI9<=$BD$6,$BD$6/10, IF(AI9<=$BD$5,$BD$5/10, IF(AI9<=$BD$4,$BD$4/10, IF(AI9<=$BD$3,$BD$3/10, IF(AI9<=$BD$2,$BD$2/10)))))))) problems: - excel formatting divide by thousands only with comma's? ,, needed divide by 10 or 100, could not find to save life, must be a way. just not in excel help for an example. - no setting for do not round??? had to use alot of if(xy,(z-0.5), til figured out to use less than < - still have problem with character/ text combo. examples a 12:18pm - <b0.63</b 12:18pm - <b10.63</b and pulling the number from that (0.63 or 10.63) quickest answer see is cut / move destination of that colum other than download "to" and somehow left/right len or istext/isnum?. thanks (sorry for the lack of info on previous.. ) "Dave Peterson" wrote: Can you use multiple cells? For instance, put your equation in A1 and then in B1: =a1*If(a11,10,100) nastech wrote: 060628 Embedding IF: depends on result.. Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
What's in the cell?
What formula did you use? nastech wrote: must be something minor, do not see why #value error. "Dave Peterson" wrote: Maybe it would be better to remove the <i and <b and </i and </b from the expression and then just look for the hyphen? Put this kind of formula in an adjacent cell: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"< i",""), "</i",""),"<b",""),"</b","") (All one cell) And then use that second suggestion to search for the hypen. But this technique will fail after a couple more HTML tags. You only get 7 nested functions. nastech wrote: ps: for text / numbers: Number / Text Combination "Custom" formatting: - is there a way to include examples (if exist), for text / number combinations: removing text from? etc. ---------- Great result on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255)) do not think would have got that for a while. is there a way to "OR" the search( on "<i", trying some variations on: =IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH(OR({"<b","<i"}),CC9)+3,255)) not quite working, will do the longer way if have to "Dave Peterson" wrote: I'd use a helper cell to display in different powers of 10. Format the cell as Number with the correct number of digits after the decimal point and the displayed value won't round if you shrink the columnwidth--but you may see ###'s if you shrink the columnwidth too much. Do you really embed the <b and </b or is that just in the post. If you embed them in the cell, you can pick out the stuff by using something like: =MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) if you want the number (not text) =--MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) If you have 12:18pm - 10.63 in the cell, you could search for the hypen and extra everything to the right. =TRIM(MID(A1,SEARCH("-",A1)+1,255)) or =--(MID(A1,SEARCH("-",A1)+1,255)) nastech wrote: hi! thanks for the response, sorry for long time back.. been busy.. have been tearing sheet formula's apart to reduce size of sheet. much work but went form 11MB to 9MB for same output of formulas. embedding was some of it, e.g.: instead of =if(a1="x",hyperlink($a$2&v9),"1",if(a1="y",hyperl ink($a$3&v9),"2")) to =hyperlink(if(a1="x",$a$2,$a$3)&v9),$a$1) not just hyperlinks.. anyways have had some grief over excel formats, custom vs formula. for 1 column version of % results (i.e. 90, see a 9) used to use 2 columns, worker column with 1 eq, e.g.: =IF(OR(BJ9={"",0},BJ9<0.02),"",((CB9-BZ9)/((CA9-0.0001)-BZ9)*10)) instead of *100 for a normal 90 result, see e.g.: 9 small problem is now 13 (130%) is what is seen. off worker column above had series of 10 formulas: if(90-.5)/10,"9" for each level of 10, made equation 10x in size. doing so much back-forth, question no longer seems to matter. (used to look like): =IF(OR(BN9="",BN9<0.02),"",IF(AI9<=$BD$7,IF($BD$7< 10,$BD$7/1,$BD$7/10), IF(OR(AI9=$BD$2,AND(AI9<(INDIRECT($BD$18)-0.5),AI9INDIRECT($BD$19))),(CE9-CC9)/((CD9-0.0001)-CC9)*$BV$19, IF(AI9<=$BD$6,$BD$6/10, IF(AI9<=$BD$5,$BD$5/10, IF(AI9<=$BD$4,$BD$4/10, IF(AI9<=$BD$3,$BD$3/10, IF(AI9<=$BD$2,$BD$2/10)))))))) problems: - excel formatting divide by thousands only with comma's? ,, needed divide by 10 or 100, could not find to save life, must be a way. just not in excel help for an example. - no setting for do not round??? had to use alot of if(xy,(z-0.5), til figured out to use less than < - still have problem with character/ text combo. examples a 12:18pm - <b0.63</b 12:18pm - <b10.63</b and pulling the number from that (0.63 or 10.63) quickest answer see is cut / move destination of that colum other than download "to" and somehow left/right len or istext/isnum?. thanks (sorry for the lack of info on previous.. ) "Dave Peterson" wrote: Can you use multiple cells? For instance, put your equation in A1 and then in B1: =a1*If(a11,10,100) nastech wrote: 060628 Embedding IF: depends on result.. Hi, is there a way to modify the following equation, to reduce from 3 legs, e.g.: =IF(equation1,same eq*10,same eq*100) thanks to something like: =equation*10(or(if<1,if10),*10 again,"") otherwise nothing purpose - single digit view, on narrow column, 10 (or 95) goes to # sign custom format: # - same in other columns with much larger equations x 3 or 4 legs will be to much. ----- actual equations in formula vary, and do not matter, still all (ulitmately <1 & 10) =IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1, ((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100))) -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Embedding IF: depends on result..
for those who wanted to know, Answer found to work: 060705 Extract number from text/number string.. Answer, for input below: =IF(CC9="","",IF(LEFT(CC9,3)<"<i", --MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255), --MID(LEFT(CC9,LEN(CC9)-8),SEARCH("<b<i",CC9)+6,255))) <i11:19am</i - <b<i0.16</i</b =MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255) Short Answer: Dave Peterson input variations a <i11:19am</i - <b<i0.16</i</b 11:35am - <b0.5601</b 11:35am - <b10.56</b 11:35am - <b1111.5601</b |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert number into words | Excel Discussion (Misc queries) | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions |