Excel Formula help

[The Freds]

Re: Excel Formula help

So just add back 2 until the next error occurs and then add 3.

 

[JohnN]

Re: Excel Formula help

Hello.

I know this isn't amateur hour, but if anyone wants to help me out, I'd appreciate it. I recently raised my prices and it has caused a problem in my spreadsheet.

I have an Excel spreadsheet for billing.

I put a person's total bill in dollars in a field.

One possible charge is $20 a day. Another possible charge is $5 an hour for four hours. Both totals are $20.

I put $20 in the field.

Is there a way I can differentiate between the two?

I use the spreadsheet to find monthly totals broken down by daily charge. I ask the program to "COUNTIF" the number is 20 and add them up to know how many $20 days a customer has and a "COUNTIF" for the hourly charge. The charge used to be $18 a day or $4 an hour, so this was never a problem since 18 doesn't equal 16. Now 20=20 and I can't differentiate.

Does my question even make sense?

Thanks in advance.

 

[JF_Gophers]

Re: Excel Formula help

Hello.

I know this isn't amateur hour, but if anyone wants to help me out, I'd appreciate it. I recently raised my prices and it has caused a problem in my spreadsheet.

I have an Excel spreadsheet for billing.

I put a person's total bill in dollars in a field.

One possible charge is $20 a day. Another possible charge is $5 an hour for four hours. Both totals are $20.

I put $20 in the field.

Is there a way I can differentiate between the two?

I use the spreadsheet to find monthly totals broken down by daily charge. I ask the program to "COUNTIF" the number is 20 and add them up to know how many $20 days a customer has and a "COUNTIF" for the hourly charge. The charge used to be $18 a day or $4 an hour, so this was never a problem since 18 doesn't equal 16. Now 20=20 and I can't differentiate.

Does my question even make sense?

Thanks in advance.

Do you have a field that says if it was hourly, or daily charge? That would be the easiest fix, then you could simply count those.

If you are just using a total field and it's $20, you won't be able to differientiate with just that field. You will need at least 1 other criteria. You could even use a hidden column for that data if you don't want it to change the way your billing looks.

 

[Shirtless Guy]

Re: Excel Formula help

Hello.

I know this isn't amateur hour, but if anyone wants to help me out, I'd appreciate it. I recently raised my prices and it has caused a problem in my spreadsheet.

I have an Excel spreadsheet for billing.

I put a person's total bill in dollars in a field.

One possible charge is $20 a day. Another possible charge is $5 an hour for four hours. Both totals are $20.

I put $20 in the field.

Is there a way I can differentiate between the two?

I use the spreadsheet to find monthly totals broken down by daily charge. I ask the program to "COUNTIF" the number is 20 and add them up to know how many $20 days a customer has and a "COUNTIF" for the hourly charge. The charge used to be $18 a day or $4 an hour, so this was never a problem since 18 doesn't equal 16. Now 20=20 and I can't differentiate.

Does my question even make sense?

Thanks in advance.

If I was you I'd have the spreadsheet have a column show hours, days, weeks, months (if you also bill in those lengths) then do a sumproduct.
https://spreadsheets.google.com/pub?key=0AvmgCQqDoSO-dFMxTi14M181RnEzWU8yUURQVVQzeEE&output=html

 

[JohnN]

Re: Excel Formula help

Thank you for your time and your responses. I was half hoping the Excel would let me flag certain entries and only count them. Oh, well.

What I've decided to do is enter the $5 x 4 hours as "$20.0001" I have a 000 button so it will be easy. I only do monthly billing for about 60 clients, so I can't imagine the rounding error will ever rear its ugly head.

Thanks again for your help.

 

[goldy_331]

Re: Excel Formula help

Thank you for your time and your responses. I was half hoping the Excel would let me flag certain entries and only count them. Oh, well.

What I've decided to do is enter the $5 x 4 hours as "$20.0001" I have a 000 button so it will be easy. I only do monthly billing for about 60 clients, so I can't imagine the rounding error will ever rear its ugly head.

Thanks again for your help.

Personally, I think you're in the wrong business where you can only bill $5/hr.

 

[MoFan]

Re: Excel Formula help

Are you assuming U.S. labor? Maybe he's paying 50 cents an hour and keeping a phat spread.

 

[JohnN]

Re: Excel Formula help

Personally, I think you're in the wrong business where you can only bill $5/hr.

Very true. I run a non-profit in a small town.

 

[FlagDUDE08]

Re: Excel Formula help

Gadzooks!

Alert Microsoft to this travesty of justice and I'm sure that will bring them around to deciding to correct their algorithm.

Microsoft uses reverse psychology with their customer service. Tell them that you absolutely love how 1900 is considered a leap year, and they'll mess with it.

 

[pirate]

Re: Excel Formula help

Very true. I run a non-profit in a small town.

I would echo the comments below that would include a field to show hourly or day...so many other functions like pivot tables and subtotals would be able to use this field, I think it would be well worth it.