| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 27520 | 2002-11-24 20:24:00 | Excel IF formula | Susan B (19) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 101037 | 2002-11-24 20:24:00 | I'm having problems with an IF formula in Excel 97. What I want it to do is have cell Y8 display an amount depending on the number in M8 as follows: If M8 is between 1-10 Y8 will be $2.00 If M8 is between 11-24 Y8 will be $3.50 If M8 is between 25-39 Y8 will be $4.25 If M8 is more than 40 Y8 will be $4.90 In cell Y8 I've tried the IF formula =IF(M8>40,4.9,2) for starters, but instead of producing $4.90 it gives $2.00 when cell M8 contains the hours 41:50 (cell formatted to hh:mm:ss). I'm not sure what Excel is disliking about the number in M8 but it won't change Y8 to $4.90 unless M8 is a ridiculously high number. Can anyone see what I'm doing wrong? |
Susan B (19) | ||
| 101038 | 2002-11-24 20:36:00 | Hi Susan It's not an easy one but I would use a text format instead of time. To further confuse things I just copied what you've done. Then I converted M8 to text. Then converted back to time and the formula in Y8 works perfectly. If you look in the formual bar you can see the differences that the formats make. |
Capt Jimbo (17) | ||
| 101039 | 2002-11-24 21:40:00 | Susan. If you try your 41:50 hh:mm:ss into general format you will see it is actually about .029 which is the number you are asking Excel to recognize as being greater than 40. The IF formula =IF(M8>=40,4.9,IF(M8>=25,4.25,IF(M8>=11,3.5,IF(M8>=1,2,"other")))) will work fine if you format the numbers you put into M8 into numbers that Excel can recognize as the ones you want. |
rugila (214) | ||
| 101040 | 2002-11-24 22:51:00 | Susan For starters, excel won't like the time format as the actual number in the cell will be less than 1 . It's the cell format that turns it into a time . Excel still looks at it as a decimal . So to make it an actual number but still represent a time is the trick . Is m8 getting it's info from another cell or are you putting the info in? If you are putting the info in then format M8 as a number, and Y8 as currency and put the following into Y8 =IF(M8>40,4 . 9,IF(M8>24,4 . 25,IF(M8>10,3 . 5,2))) . That will give you the right answer . If the cell is getting the info from elsewhere and the elsewhere is the result of adding/subtracting two times, then you will have to multiply the result in m8 by 24 to turn the decimal into a number greater the 1 . Hope that is of help . Craig . |
Craigb (688) | ||
| 101041 | 2002-11-25 00:26:00 | Also... It might be worthwhile (once you have sorted out the best way to use numbers as opposed to time) if you were to use a lookup table instead of the IF statement. This will make things a lot easier to update in future when the prices? are increased ;) |
Shroeder (492) | ||
| 101042 | 2002-11-25 01:00:00 | Actually Susan, you may not need to change the time format See whitbycricket.orcon.net.nz (hope that link works!) You would need to work out at which point you want cost to change (your 1-10 hours and then 11-24 hours doesn't allow for 10 hours 30 minutes!) I have based my little sheet on 10:00:01 being at the increased price ;) |
Shroeder (492) | ||
| 101043 | 2002-11-25 20:39:00 | Hi Susan Can you let me know when you have accessed that file as I will then be able to remove it from the server. Thanks |
Shroeder (492) | ||
| 101044 | 2002-11-26 01:37:00 | Thank you for the replies, people . I've brought the worksheet home and will play around with the suggestions to see what works . Shroeder, I have got that file now thanks, and will have a good look at it soon . :-) |
Susan B (19) | ||
| 101045 | 2002-11-26 01:58:00 | You could try This Way (www.orderofthelion.com). What's the idea of taking work home, anyway? You've got a new computer to corrupt the software on. | Graham L (2) | ||
| 101046 | 2002-11-26 02:04:00 | >>What's the idea of taking work home, anyway? To earn money for the network and soundcards of course. :D BTW, that's a bad link. :-( |
Susan B (19) | ||
| 1 2 | |||||