Yes, in this case, you should divide by C6, as it certainly simplifies things. You can also do this for C6=1 as a division by 1 will not hurt anything. So your equations are now:
1 – if C7 =6 hours, 8.50. IF C7 <>6, multiply C7 by 1.40 divide by C6
2 – if C7 =6 hours, 4.25. IF C7 <>6, multiply C7 by 1.40 divide by C6
3 – if C7 =6 hours, 2.85. IF C7 <>6, multiply C7 by 1.40 divide by C6
4 – if C7 =6 hours, 2.85. IF C7 <>6, multiply C7 by 1.40 divide by C6
5 – if C7 =6 hours, 2.85. IF C7 <>6, multiply C7 by 1.40 divide by C6
6 – if C7 =6 hours, 2.85. IF C7 <>6, multiply C7 by 1.40 divide by C6
So you have a few nested IF calls. Look at
=IF(C7=6,IF(C6=1,8.5,IF(C6=2,4.25,2.85)),C7*1.4/C6)
Where
"IF(C7=6,...,C7*1.4/C6)" is the =/<> 6 hours check and if not 6 hours, then it's a straight calculation.
"IF(C6=1,8.5,IF(C6=2,4.25,2.85))" is used when C7=6, and checks for C6=1 (result 8.5) or if not, then check C6=2 (result 4.25). If neither of these, then it goes to the last part of the second IF call giving a result of 2.85.
This works OK, when you don't have many selections for 6 hours based on C6. If each value of C6 generated a different result, then this formula would become too complex for humans to read easily, and lookups should be used instead. These are easy enough, but totally different.
All that said, I can't see a consistency between your =6 hour amounts and your <>6 hour calculations, so I wonder if your list is correct. If it's more complex than you've shown, then post again and we'll discuss lookups.
Regards, Dave S
----- Original Message -----Sent: Wednesday, August 26, 2015 6:11 AMSubject: Re: [ExcelVBA] Multiple IF Statements in Excel 2007Not exactly sure what you want here.Can you give a bit more detail please?For example... What does (or C6?) mean?Where does the result go?Thanks for telling us you had cross posted? Is that a long list at all?If not then can you supply the links please?Lisa
-----Original Message-----
From: talacal@yahoo.com [ExcelVBA] <ExcelVBA@yahoogroups.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Tue, Aug 25, 2015 9:28 pm
Subject: [ExcelVBA] Multiple IF Statements in Excel 2007
This may not be a VBA-related issue but I am struggling right now and I need your help. I posted this to other Excel groups but I am not getting any response from them.Here's the scenario: when the user enters a number from 1-6 in C6, I want the program to calculate and give me the answer based on the hours inputted and other info.C7 - Hours (users enter hours from 1 to 50)F7– where the formula or result should beWhen user enters the following numbers1 – if C7 =6 hours, 8.50. IF C7 <>6, multiply C7 by 1.402 – if C7 =6 hours, 4.25. IF C7 <>6, multiply C7 by 1.40 divide by 2 (or C6?)3 – if C7 =6 hours, 2.85. IF C7 <>6, multiply C7 by 1.40 divide by 3 (or C6?)4– if C7 =6 hours, 2.85. IF C7 <>6, multiply C7 by 1.40 divide by 4 (or C6?)5– if C7 =6 hours, 2.85. IF C7 <>6, multiply C7 by 1.40 divide by 5 (or C6?)6– if C7 =6 hours, 2.85. IF C7 <>6, multiply C7 by 1.40 divide by 6 (or C6?)Notice numbers 3 to 6 have the same condition. Is there a one formula for them instead of repeating the formula? I use Excel 2007. Thank you very much and hope to hear from you soon!._,___
__._,_.___
Posted by: "David Smart" <smartware.consulting@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
.
__,_._,___
Tidak ada komentar:
Posting Komentar