bhatlas@pyrite.som.cwru.edu (Sunil Bhatla) (02/25/91)
How do I get WingZ to return a user-specified value (say 8.75) if it finds a text string (say B+) in a given cell? The IF(condition,x,y) function specified in the manual only handles numbers, not text strings. My problem is that I have a bunch of letter grades that I need to compute averages for. Any ideas will be appreciated (yes, I know there are programs out there that will do just this, but I would rather stick with WingZ - if Lotus 123 can do it, why can't WingZ!). Thanks in advance, Sunil. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Sunil Bhatla ||| INTERNET: bhatlas@pyrite.som.cwru.edu Case Western Reserve University ||| VOICE: 216/368-2061 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
toms@utoday.com (Tom Smith) (02/26/91)
In article <1991Feb25.022254.23689@usenet.ins.cwru.edu> bhatlas@pyrite.som.cwru.edu (Sunil Bhatla) writes: > >How do I get WingZ to return a user-specified value (say 8.75) if it >finds a text string (say B+) in a given cell? The IF(condition,x,y) >function specified in the manual only handles numbers, not text >strings. > >My problem is that I have a bunch of letter grades that I need to >compute averages for. Any ideas will be appreciated (yes, I know there >are programs out there that will do just this, but I would rather >stick with WingZ - if Lotus 123 can do it, why can't WingZ!). > I agree with you that the Wingz Manual is not very well organized, especially with regards to Functions. The reference leaves a lot to be desired !! Anyway, as a solution to your problem, I would set up a small table in an unused portion of your spreadsheet and use the VLOOKUP function to determine the values for your grades. For example.... AA | AB | ---------------------- 1 | A+ | 10 | 2 | A | 9 | 3 | A- | 8 | 4 | B+ | 7 | 5 | B | 6 | etc... Then put the formula VLOOKUP(A5,AA1..AB5,1) in the cell which you want the grade value to appear where: A5 = Cell where Grade text is AA1..AB5 is range for the table which has been created 1 is the offset into the table. So when VLOOKUP Scans your table, it will look in column AA for a value matching the grade text, and will return the value located in the same row offset by one column (in this case AB) Now as I do not have a manual in front of me, nor would it probably do much good if I did, please bear with me if the next example does not perform correctly without a little research. IF VLOOKUP fails, i.e. does not find a value in your table. It will return an error code of 26 and display a message. If you can deal with this, as you would be using the application and would not be confused by the error, it would be in your best interest to do so. Otherwise, I believe the following will work ;-) IF(ERRNR(VLOOKUP(A5,AA1..AB5,1)) = 26,"ERROR",VLOOKUP(A5,AA1..AB5,1)) Even without a great reference manual, this product definitely has its advantages. (I find the online help to be useful, Mostly for function definitions as above) Good Luck with Wingz, Tom Smith