=IF(C17>=0, (G16+(D17/F17)), (G16+(J16*C17))) In the cell where formula is located, the number there represents the middle calculation above ( the G16+(D17/F17) My question is, what does this formula represent that it chooses only the middle formula and not the one on the right> ?
The value in C17 is greater than 0, so it runs the “true” formula. Any other mind benders for us?
If I’m reading your question right, it means that c17 is greater to or equal to 0 since it chose to use the first part of the if then statement. Else it would have used the second formula.
Since people are giving excel advice here, I have a question as well. Which excel function would one use if you have a lot of true/false conditions (and corresponding formulas)? For example, lets I have a whole bunch of letter grades (As, Bs, Cs, etc) and I want to convert them to a 4.0 scale. How would I automatically make it change the A+'s to 4.0, the As to 3.8, the B+'s to 3.3, etc?
I can think of two ways. The not-so-elegant way is to use if functions embedded into each other. Let’s say that your grade is is A1: if(A1=‘A+’,4,if(A1=‘A’,3.8,if(…))) The more elegant way is to design a table with the letter grades in the first column and the scores in the second column. Then, each time you want to make the change, use the vlookup function: lookup_value is the letter grade you want to convert table_array is the two-column table mentioned above col_index_num is 2 (as the scores are in the second column) I hope it helps.
=LOOKUP(A1,{“A+”,“A”,“B+”,“B-”},{4.0,3.8,3.3,3.0})
frenchriviera Wrote: ------------------------------------------------------- > use the vlookup function Agreed. You definitely don’t want to use a long-winded if/then statement for this. Plus, Excel has a limit of nested if/then statements in the same formula. As frenchriviera said, on another tab in the same workbook, simply make two columns. First column has all the possible grades and the second column has the corresponding GPAs. I usually name my arrays because it’s easier for me when writing formulas, so name columns A and B something like “grades”. Then on your original tab, on the cell to the right of each letter score, the formula should be (assuming the first letter grade is in A1): =vlookup(a1,grades,2,0) By having a translation table on the second tab, you can easily update the corresponding point values should they change. You definitely don’t want to have to go back into your formulas to change these assumptions (or if you wanted to add more possible grades) if you went the if/then method…
You can next something like 40 IF statements in Excel 2007. It’s crazy, I had an IF statement at my old job that had 15 nested formulas. I could have used an Array Lookup, or something more elegant but I was so bored there I wanted to make the most complicated formula possible, now that I am gone I cannot imagine them knowing how to repair the formula when it inevitably breaks.
nuppal Wrote: ------------------------------------------------------- > You can next something like 40 IF statements in > Excel 2007. It’s crazy, I had an IF statement at > my old job that had 15 nested formulas. > > I could have used an Array Lookup, or something > more elegant but I was so bored there I wanted to > make the most complicated formula possible, now > that I am gone I cannot imagine them knowing how > to repair the formula when it inevitably breaks. Haha haha I do the same thing. Job security
nuppal Wrote: ------------------------------------------------------- > You can next something like 40 IF statements in > Excel 2007. It’s crazy, I had an IF statement at > my old job that had 15 nested formulas. > > I could have used an Array Lookup, or something > more elegant but I was so bored there I wanted to > make the most complicated formula possible, now > that I am gone I cannot imagine them knowing how > to repair the formula when it inevitably breaks. Lol, they’ll say: “MoFo nuppsssss!”
^ They will get the “#N/A” about 5 seconds later when they realize the useless piece of information they are looking for won’t calculate you’ll hear a; FUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU!