MS Excel Help
 

Let's see how diverse FT is @:-)

I have 2 columns of numbers, A and B...

How do I tell Excel to plug the higher number of A and B into column C?

Have the contents of C1 be "=IF(A1>B1,A1,B1)" and so on for A2,B2,C2....

Another question: How do I ask for the sum of (ColA) + (higher of Col B or C) + (higher of Col D or E)?

=(a1+if(b1>c1,b1,c1)+if(d1>e1,d1,e1))

Many thanks! Trying to decipher what that translates to... is it "If A1 is greater than B1, then A1, otherwise B1"?

Yeah, exactly. What the if() function does is it evaluates the first argument, let's say A1>B1, and if it's true, it outputs the value as the second argument, A1, and if it's false, it outputs the value as the third argument, B1.

Nice... thank you so much for your help! I owe you a drink in the AC ^

The "+" didn't work... took a while, but realized that those have to be "," in the SUM argument. Still, thanks for leading me the right way.

Odd, just tried it in Excel over here and it worked using +.

It must be my Excel (stupid MS!)... I wouldn't doubt it. Actually, the only reason I started learning how to use Excel was to keep track of my mileage....

FT STRIKES AGAIN! :cool:

That works, but here is also an easier way

=max(a1,b1)

it returns the max of any of the arguments there, and you can go as many cells as you need to.

You can do the same thing with the total clls.

For more then two columns, the max command is much easier then nesting if then's.

Great... you post just as I'm done with my little project and straining my brain to include all my nested arguments (going in to the 4th level :eek: ) :p

Good to know for next time. :)

Sorry, it's been a long busy day, first time on.

Simpler than previously posted solution, using the MAX function that cordelli beat me to on the first question:

=A1+max(B1,C1)+max(D1,E1)

You can do some pretty fancy things with IF, nesting one inside another if you have to, but there's usually a simpler way to do simple things.

Just a side note... an excellent Excel reference, www.MrExcel.com

Looks like there are a few Excel experts here so I might as well ask my own Excel question.

Is it possible to evaluate the contents of a cell and if it's <A the font color should be blue while if it's >A the font color should be green? TIA.

Format -> Conditional Formatting

First, format the cell as blue. Then:
From there:
- In the first pull-down box, leave the default "Cell value is" alone
- In the second one, select "Greater than"
- In the fill-in field, enter the value of A (can be another cell reference)
- Click the "Format..." button
- Make the text color green
- Click OK twice: once for the format, once for the conditional formatting

BTW, your conditions left out the value=A case. The above will leave it blue. If you want it green, select "Greater than or equal to" in the second step. If you want to treat it separately, say purple, click the "Add>>" button after you finish these steps to specify another condition. You ought to be able to handle what happens next yourself.

Wow.. resurecting an old but useful thread with a new question.

Let's say I have % grades in Col A, how would I make B display A/B/C/D/F depending on the percentage?

So far, I have =if(A1>90,"A",???). I seemingly can't nest another IF argument so that I can say, "If A1>80, then B, but if not then if A1>70, then C...." and so on. I'm overthinking this, aren't I?

Actually you can nest IF statements... up to 8.

I'm sure there is a better way, but I do not know of one.

Yeesh, then what am I doing wrong? Here's my formula:

=IF(F2>90,"A",(=IF(F2>80,"B",(=IF(F2>70,"C",(=IF(F 2>60,"D","F"))))))

(My % scores are in F2).

Try this:
=IF(F2>90,"A",if(F2>80,"B",if(F2>70,"C",if(F2>60"D ","F"))))))

Success! Many thanks ^

Set up a table like this (say in cells A1 to B5) - substitute your own numbers for the grades (maximum number for each grade and should be sorted worst to best).

Say your score is in column A from row # 8 down.
Then to get a grade use the following forumula
"=index($b$1:$b$5,match(a8,$a1:$a5,1))"

Works much better than nested IF statements, especially when your table has hundreds or thousands of entries ;)

Ooooh... that is good. That will totally help out my other spreadsheet. I knew there had to be a better way!! :)

Ok, now you got all advanced on me! I'll have to play around with that, thanks!



Would this be for a mileage tracking spreadsheet? :D

Here's a question for you...

That previous code for matching will come in handy for sure... but I want to take it up one notch.

I have a spreadsheet with client names. Next to the client names are bill codes. Hidden on another sheet is a list of 100 bill codes, and what they mean. I want to be able to put a bill code in next to the client name, and then at the very end of the spreadsheet, have a list of the descriptions of all the applicable bill codes.


For example:

1 - Bill Smith -- ZGR01
2 - Joe Schmoe -- ZGRRV
3 - Sandy Dandy -- GNADA
======================
ZGR01 - Everything
ZGRRV - Rm/Tx/VT
GNADA - Nothing




Is that possible?

Yup. In this case your table of descriptions doesnt need to be sorted, but make sure your code entries match exactly. Then inside the "match(lookup,table,#)" put 0 (instead of the 1 above). This finds exact match.

I usually like to put into the formula some code in case there is no exact match (eg typos or new codes). Something like this would do it.

=if(iserror(match(a8,$a$1:$a$5,0)),"Error - no entry",index($b$1:$b$5,match(a8,$a$1:$a$5,0)))

I've got 2 perplexing questions :)

1) can I justify a cell fully so that the text stretches across it?
Example:

|h a p p y| rather than |happy | where | and | are cell boundaries.

2) If I have a group of cells, and I want to copy them somewhere else on the sheet if a certain condition is met, is this possible?

Suppose I have 3 cells. In the first cell is "A" in the second cell (beneath the first cell) is "B" and in the third cell (next to the first cell) has a number.

If that number is greater than 100, I want to copy all 3 cells to a new place.

Thanks :)

1) I don't know of any way to do this.

2) Enter formulas like these into the two conditional destination cells:

=if(third cell > 100,first cell,"")
=if(third cell > 100,second cell,"")

The "" puts a null text string (i.e., nothing at all) in the destination cells if the condition in the IF function is not satisfied.

Justify should do something like that, but it doesn't seem to work for me.

would it be possible to do this with times?

For example:

if time in third cell > 2:00pm?

Thanks again,

yes but it depends on how you have entered/formatted the third cell

if it is done as text and using 24 hour naming then if(third cell >"1400",first cell,"") works
if it is done as number and using 24 hour naming then if(third cell>1400,first cell,"") works (note no "" around the criteria)
if it is done as time and no date is entered then Excel defaults to 0/1/1900 date and 2pm is 14/24 so if(third cell>(14/24),first cell,"") works
if it is done as time and date is entered, and assuming you dont care about the day then if((third cell - int(third cell))>(14/24),first cell,"") works

Justify only works on (a) cells with word wrap enabled, and (b) not on the last line. If you only have one line, it's not justified.
The first column is left-aligned, and the second is justified.

I think you mean left-aligned, which is what General defaults to for text, but in any case those were exactly the problems I ran into when I tried to use Justify to make this happen. The only thing I can suggest is that, if this is a one-time deal and the text to be stretched out won't change, experiment with spaces between the letters to see if that might look OK. Caution: if the spreadsheet might be used on other computers that could have different versions of the fonts or other system-level differences, the results of this sort of hard formatting can look weird.

The connection at NRT was incredibly smooth today and I ended up with some extra time waiting for my flight. Here's one way to justify the text:

1. Enter "H a p p y sesquipedalianthology" into the cell. There is one space between each letter of the text you want to justify, another one after it. The last part can be any string that's too long to fit into one cell width. If "Happy" with a space between each letter is too big, double-click the cell to activate in-cell editing and set the spaces to a smaller point size.

2. Format the cell to justify horizontally, to align at the top vertically, and to wrap.

3. The row will become deep enough to show all the text, including the junk word. Grab it by the row number at the left and squeeze it back up so only the row you want shows.

Let us know if it works for you!

Can I ask another.

I have some substantial Excel sheets with a lot of aircraft data on them. The person who put them together only put the aircraft serial number (one of the columns) against the first line for such, any additional lines (from none to say 20 of them) and the serial number column is just blank.

This is fine for just looking at the info, but if you want to sort the sheet on one of the other data columns to bring common things together, the serial number reference is then lost.

So, can anyone suggets a formula which says "if the cell is blank, then make it the value of the cell above, if that has something in it". Probably best to add an extra column to do this.

If I understand the question, you have:
And you want:
Insert a new column A. Make a1 = B1. Make a2 = '=IF(B2="",A1,B2)'. Copy A2 and paste into the rest of column a. Either hide B, or copy column a, do a paste special-> values into a new column, and delete a and b.

Murphy, thank you very much, you are correct in your supposition, a bit busy at the moment, will give it a shot later.

Murphy, thanks for that, works fine, I just never worked out the syntax of the IF statements before, have been able to do some others now :)

Another question for everybody.

I am Windows 2002. My cells are format Text, with Wrap Text enabled. I can enter up to 255 characters of text in a cell, anything beyond that and the whole cell just shows all ###### although when selected the full cell contents are correctly shown in the formula bar.

But I read the Excel spec is a cell limit of 32,768 characters, of which the first 1,024 characters are displayed in the cell. Any reason for the shortfall I am getting ?