Posted: 6/10/2008 7:43:26 AM EDT
|
Okay...worked with Excel some...have a problem I need to enter... I want to use and "if" statement... Actually want to enter the following: If "cell a5" = 1.6 then "cell d5" /45, if "cell a5" = 1.5 then "cell d5" /120, if cell a5" =1.4 then "cell d5"/150, if "cell a5" =1.3 then "cell d5 /180, if "cell a5 =1.2 then "cell d5 /210 Plane language if cell a5 equals 1.6 then divide cell d5 by 45, if cell a5 equals 1.5 then divide cell d5 by 120..and so on. This would actually go on to an if cell a5 0.6 then divide cell d5 by 720, but I think if I had the first few ifs and a way to end it I could do the rest. Thanks!! |
|
Planely you need to put the excel spreadsheet on a treadmill. Why do you need a bunch of if statements - can't you just put a formula to divide the proper cell by the proper number? You might look at Excel macros - i hope you know VB! support.microsoft.com/kb/213630 |
+1 VBA macros for the win. example. (It has been a while since I did this. I used to be an expert.  )'where A6 is just a cell with which to do math. ETA cell("A5").Value is incorrect. but it is something like that. I can't remember. |
|
What is the problem? You need and IF, THEN, ELSE statement. I can do it, but I cannot explain it =IF(E46>P45,"A",IF(E46>P46,"B",IF(E46>P47,"C",IF(E46>P48,"D","F")))) This is a statement that I wrote that compares cells with grades and assigns a letter grade. Does that help at all? The P45 through P48 are simply ranges for the different grades. |
|
Needs more information. Do you have a column of numbers you're checking against? If so, it sounds like you need to set up some additional columns to sort out the numbers into multiple columns. Provide more info on the raw data and suggestions can be made as to how to handle it in the simplest way. |
=if(A5=1.6,D5/45,if(A5=1.5,d5/120,if(A5=1.4D5/150,if(A5=1.3,D5/180,if(Ar=1.2,D5/210,"no match"))))) |
|
I am actually trying to make a table that will calculate the NOAA Oxygen CNS tracking for Scuba Diving. I need a column for TIME, a column for PO2, then a formula that reads the PO2 then decides to divide the TIME column by the Exposure value for the PO2 the formula chose. The NOAA Table (part of it) PO2 Exposure 1.6 45 1.5 120 1.4 150 1.3 180 Basically, the TIME & PO2 columns will change...so I need a formula that will read what the PO2 number is in the column then take that PO2 number and divide the TIME column by the Exposure number for the chosen PO2. example... if the PO2 in the column is 1.6 and the TIME column is 20 I need 20/45. example....if the PO2 in the column is 1.4 and the Time column is 15 I need 15/150. example... if the PO2 is 1.5 and the Time is 30 then I need the formula to divide 30 by 120. |
This is correct |
|
yeah, straight vlookup is easier. Don't even need IF statement Two columns make up your lookup range: 1.6 45 1.5 120 1.4 150 1.3 180 call the range "look" then in Col A have 1.5, 1.4 or whatever Col B have the time in question then Col C the formula is: =B1/(vlookup(A1, look, 2, false)) what do I win? |
Correction: Mitch forgot a comma =IF(A5=1.6,D5/45,IF(A5=1.5,D5/120,IF(A5=1.4,D5/150,IF(A5=1.3,D5/180,IF(Ar=1.2,D5/210,"no match"))))) Tested in excel, and works |
I can get it to work in a new page, but when I try plugging it into my current form (shown above) it doesn't work. I am changing the cell values to the appropriate ones. Question: I have other cells reading the same cells I am trying to use for other calculations. Is there a way to set order or something like that..maybe it is reading it before the value actually gets entered? Can I email this form to someone who knows what they are doing? Because I obviously can't figure it out. |
|
=+IF(A5=1.5,D5/45,(IF(A5=1.5,D5/120,(IF(A5=1.4,D5/150,(IF(A5=1.3,D5/180,(IF(A5=1.2,D5/210,"NO MATCH"))))))))) Copy and paste this into your cell EXACTLY. If you look at the other guy's solution, in the final if statement he has "Ar" instead of "A5"... Freudian slip, perhaps. Tell us the result. |
It comes up "NO MATCH" |
A HUGE THANK YOU! for helping me...now..one more question....for now..lol. Have another question...I need to take that formula clear out to 0.1. When I get to 0.9 in the spreadsheet it no longer highlights the cells / and tells me there is an error. Is there a limit on how long a formula can be? If this is the case....how can it get from 1.6-0.1 in one cell formula. |
|
Got this to work: =CONCATENATE(IF(E38=1.6,C38/45,),IF(E38=1.5,C38/120,),IF(E38=1.4,C38/150,),IF(E38=1.3,C38/180,),IF(E38=1.2,C38/210,),IF(E38=1.1,C38/240,),IF(E38=1,C38/300,),IF(E38=0.9,C38/360,),IF(E38=0.8,C38/450,),IF(E38=0.7,C38/570,),IF(E38=0.6,C38/720,)) However, it is stretching it out like 15 decimal places...how do I get it to a percentage or fewer decimal places? Formating the cell isn't working. |
