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

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

Zack Barresse

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

MRdNk

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.

=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.

=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.

MRdNk

Zack Barresse is a bit more refined, but essential does the same thing.

MRdNk

lol!!! :d
Oops meant to put: Zack Barresse's a bit more refined...

LOL.

Zack Barresse

Hey slurp, you should listen to MRdNk, I think they're onto something here..

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

MRdNk

OH NO! What have I done? You'll be adding it, to your Signature next.

slurpee55

Probably "refined" like sugar, flour or this....

slurpee55

MRdNK, have you ever checked out his signature at the VBA Express Forum?
Zack's a self-proclaimed Urban Myth! Now a Refined Urban Myth!!!!

MRdNk

Probably "refined" like sugar, flour or this....
"The Refined Feline - The Refined Litter Box" - is amazing! lol. - gonna email that to my work email address.

"Urban Myth" -

Gingerd10

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

I don't think the bottom of my info. looks right
that is how my spreadsheet looks somewhat.

slurpee55

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:

bomb #21

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

• 13.5 KB Views: 148
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.

As Seen On