i want to set my electricity bill formula if get 1002 units bill then i want 7535 amt in one cell, i unable to set logical condition so please help me my units rate as per following
Units
0-100 3.36
101-300 6.05
301-500 7.92
501-1000 8.78
1001 and above 7.50
Set up a 2 column table like this:
0.........3.36
101......6.05
301......7.92
501......8.78
1001....7.50
Assume that table is in the range D1:E5.
A1 = 1002
This formula entered in B1:
=LOOKUP(A1,D1:E5)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
its not like that i think u never understand my question
Yeah, that's possible.
Try describing what you want to do again and include a couple of examples along with the results you expect.
Hello,
I am pretty sure if you are choosing a rate basing on a number, the formula that Biff suggested on post #2 is a very good start.
But, yes, I also can misinterpret your problem because everything useful information out of your description was "........ if 1002 units ... 7535 amy ...."
This is a friendly advice also, we are not sitting right next to your or reading your workbook right now, so it is for granted that we might not fully understand your question, and sometime can even misinterpret it, but please do not say harsh words such as "i think u never understand my question". Biff is taking his free time and putting efforts to help you, and if you keep saying stuff like that to him or everyone else, I don't think that will benefit you in any way.
For best results, upload a sample file with dummy data as Biff suggested on Post #4.
(copy pasta from Ford)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
Regards,
Lem
You're looking for a Tiered commission type of thing..
For the first 100 you get 3.36
for the next 200 you get 6.05
For the next 200 you get 7.92
etc...
Try this with the same table Tony suggested in D1:E5
=SUMPRODUCT(LOOKUP(ROW($D$1:INDEX($D:$D,A1)),$D$1:$E$5))
A1 = 1002
DO NOT CHANGE the D1:INDEX(D:D part it's not related to the data.
sorry for using harsh language uploading sample file
OK, change the values in B9:B13 to just
0
101
301
501
1001
And your formula in E14 is
=SUMPRODUCT(LOOKUP(ROW($D$1:INDEX($D:$D,$D$14)),$B$9:$C$13))
im not satisfied yet
See if there's anything at this site that helps:
http://mcgimpsey.com/excel/variablerate.html
Last edited by Tony Valko; 05-03-2013 at 08:44 AM.
That's not surprising.Originally Posted by ganeshadeshpande
Is it just that you don't want to have the table on the sheet? You want it all done in one cell?
The table can be coded into the formula..
Try
=SUMPRODUCT(LOOKUP(ROW($D$1:INDEX($D:$D,$D$14)),{0;101;301;501;1001},{3.36;6.05;7.92;8.78;7.5}))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks