Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Gingerd10

Thread Starter
Joined
Oct 7, 2008
Messages
8
I am working on a formula for a water bill based on rules, Gallons used must be greater than 25,000 otherwise the bill is 0
the billing rate varies based on COM, NPROFIT,GOV
COM is $3.00, NPROFIT is $2.00 and GOV is $1.50 per thousand gallons used. I have figured a formula for
I =IF(AND([Cust Type]="NPROFIT",[Gal Used]>25000),G2/1000*'Billing Rate'!C4,0)
=IF(B5="GOV",G5/1000*'Billing Rate'!C5,0)
=IF(B73="COM",G73/1000*'Billing Rate'!C3,0)

I dont know how to combine these and I also dont know how to get the bill to be 0 if it is under 25000 gallons.

Any help?

This is way confusing
Thanks
 
Joined
Jul 25, 2004
Messages
5,458
Hi there, welcome to the board!

Not sure what your formulas are, the references don't look right. Did you copy/paste from another location? With the information you've given, you may want to try something like this...

=IF(A5<25000,0,A5/1000*IF(B5="COM",'Billing Rate'!C3,IF(B5="NPROFIT",'Billing Rate'!C4,IF(B5="GOV",'Billing Rate'!C5,1))))

If this doesn't work or you need more please post back and let us know more details. A sample file might help as well.

HTH
 
Joined
Apr 7, 2007
Messages
439
Hi Gingerd10,

Welcome to the forums.

Down to business, the basics of the IF statement is "IF Condition", then TRUE, else FALSE. Now what you need to do is called a NESTED IF statement, as in nest other if statements between others.

So start with:
=IF([Gal Use]<25000, 0, ...)

So if Gal Used is under 25000 then value is 0 else do something else.

For the ... put in your other if statements, so firstly the NPROFITs:
IF([Cust Type]="NPROFIT",G2/1000*'Billing Rate'!C4,...)

So you have:
=IF([Gal Use]<25000, 0,IF([Cust Type]="NPROFIT",G2/1000*'Billing Rate'!C4,...)

If less then 2500, value is 0, else (over 25000) if cust type = NPROFIT, then calculate value, else do something else.

For the last example I'll add both of your existing formula
=IF([Gal Use]<25000, 0,IF([Cust Type]="NPROFIT",G2/1000*'Billing Rate'!C4,IF([Cust Type]="GOV",G5/1000*'Billing Rate'!C5,IF([Cust Type]="COM",G73/1000*'Billing Rate'!C3,"ERROR")

In Pseudo programming code you would write it:
Code:
IF Gal Use < 25000 Then
[INDENT]Value = 0[/INDENT]
Else
[INDENT]IF Cust Type = NPROFIT Then
[INDENT]Value = G2/1000*'Billing Rate!C4[/INDENT]
ElseIF Cust Type = GOV Then
[INDENT]Value = G5/1000*'Billing Rate!C5[/INDENT]
ElseIF Cust Type = COM Then
[INDENT]Value = G73/1000*'Billing Rate!C3[/INDENT]
Else
[INDENT]Error[/INDENT]
[/INDENT]
End IF
When converting this into an Excel formula, we don't write then and else, we use a comma, and in stead of elseif, we nest an if statement after the else part of the formula.

If statement, (TRUE) then, (FALSE) else.

I hope this is a good explanation of how this works. Excel if statements were probably how I initially got into programming.
 
Joined
Apr 7, 2007
Messages
439
Zack Barresse is a bit more refined, but essential does the same thing.
 
Joined
Jul 25, 2004
Messages
5,458
Hey slurp, you should listen to MRdNk, I think they're onto something here.. ;)

Refined. Just rolls right off the tongue, doesn't it? :D
 

Gingerd10

Thread Starter
Joined
Oct 7, 2008
Messages
8
Ok that didn't work..Maybe I need to explain it differently.
Ok I am calculating a water bill based on rules if a customers bill is waived place 0 in water bill column
gal used (gallons used) must be greater than 25,000 gallons during the quarter otherwise its o
for all other accounts the billing rate varies based on the type of customer
The billing rates are $3,[email protected], or $1.50 per thousand gallons used depending on the type of customer for example a commercial customer using 75,000 gallons has a water bill of $225(75*3), whereas a government customer using 100,000 gallons pays $150(100*$1.50) A commercial customer using 15,000 gallons has a water bill of 0

Maybe this helps some.I will be enetering a formula in Water Bill
CustomerName Cust Type Bill Waived Taxable Billing Year Billing Quarter Gal Used Water Bill Tax Total Bill Red Lake Chapter NPROFIT No Yes 2010 1 108,300 $

$ $ Amity Church NPROFIT Yes No 2010 1 55,160 $ $ $
 

Gingerd10

Thread Starter
Joined
Oct 7, 2008
Messages
8
I don't think the bottom of my info. looks right
that is how my spreadsheet looks somewhat.
 
Joined
Oct 20, 2004
Messages
7,837
Ginger, if you can upload a copy of your file (click Go Advanced, then go to Manage Attachments and a pop-up will open that will allow you to browse for the file and upload it.) If there is sensitive data in it, replace it with nonsense data of the correct type, so we can see the layout. Also, please write instructions into the file including what the results you want are for several scenarios.
If you find you can't upload a file, please click on my name and go to my profile and send me an email with your return address. I will respond and then you can send me the file and I will upload it for you.
If you have to do that, please also include the URL of this thread:
http://forums.techguy.org/business-applications/757065-excel.html#post6183662
 
Joined
Jul 1, 2005
Messages
8,546
Gingerd10 said:
I don't think the bottom of my info. looks right
that is how my spreadsheet looks somewhat.
Doesn't look entirely right. As far as I can tell you're working with 10 columns.

Sometimes the best way to construct a complex formula is to do it in stages and in order of "applicability" -- which Zack's formula does by identifying the factor that applies to all cases (a "constant") first.

Zack's formula simplified says:

=IF(GalUsed<25000,0,x)

I'd switch that around (to better reflect what you said first) to:

=IF(GalUsed>25000,x,0) -- see H2 of the attached.

So that's your first constant. Your second is, where the bill is not waived, GalUsed must be divided by 1000. Which is:

=IF(GalUsed>25000,GalUsed/1000,0)

See H3 of the attached.

Then you have the third and final component of the formula, which is actually your only variable; where the bill is not waived, GalUsed must be divided by 1000 and multiplied by the rate according to CustType. Which is:

=IF(GalUsed>25000,GalUsed/1000*CustType,0)

You can derive rate according to CustType by using a small lookup table (yellow cells of the attached). Bolt those components together to get:

=IF(GalUsed>25000,GalUsed/1000*VLOOKUP(CustType,value from column 2 of lookup table,FALSE),0)

See H4 of the attached, which is the "final version". Go through the various CustTypes in B4 to see it "in action".

The lookup table can be wherever you want, just have it somewhere "out of the way".

HTH :)
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top