Embedding IF: depends on result..

[nastech]

060628 Embedding IF: depends on result..

Hi, is there a way to modify the following equation, to reduce from 3 legs,
e.g.:
=IF(equation>1,same eq*10,same eq*100) thanks

to something like:
=equation*10(or(if<1,if>10),*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.

 

[Dave Peterson]

Can you use multiple cells?

For instance, put your equation in A1 and then in B1:

=a1*If(a1>1,10,100)

 

[nastech]

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",hyperlink($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),AI9>INDIRECT($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(x>y,(z-0.5), til
figured out to use less than <

- still have problem with character/ text combo. examples are:
12:18pm - <b>0.63</b>
12:18pm - <b>10.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]

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]

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:
<i>11:19am</i> - <b><i>0.16</i></b>
11:35am - <b>0.5601</b>
11:35am - <b>10.56</b>
11:35am - <b>1111.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

 

[nastech]

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

 

[nastech]

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]

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.

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]

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]

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 - <b>0.5601</b> <i>11:19am</i> - <b><i>0.16</i></b>

first half works, not the second... don't get that

 

[Dave Peterson]

I would use that helper cell and elimate that HTML junk before I tried to
extract the number.

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 - <b>0.5601</b> <i>11:19am</i> - <b><i>0.16</i></b>

first half works, not the second... don't get that

 

[nastech]

must be something minor, do not see why #value error.

 

[Dave Peterson]

What's in the cell?

What formula did you use?

must be something minor, do not see why #value error.

 

[nastech]

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

<i>11:19am</i> - <b><i>0.16</i></b>


=MID(LEFT(A1,LEN(A1)-4),SEARCH("<b>",A1)+3,255) Short Answer: Dave
Peterson

input variations are:
<i>11:19am</i> - <b><i>0.16</i></b>
11:35am - <b>0.5601</b>
11:35am - <b>10.56</b>
11:35am - <b>1111.5601</b>