• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problems with an addition formula

B

btc909

New Member
Example 1: Customer pre-pays for 5 bulk hours, customer used 16.75 hours, the answer should be 11.75. This is what i'm using =IF(B3<=5,(B3),(B3-C3))


B3 (Bulk Hours) is 16.75 - C3 (Pre-Paid Bulk Hours) is 5 - D3 (Overage Bulk Hours)should be 11.75.


Example 2: Customer pre-pays for 5 bulk hours, customer used 2 hours, the answer should be 11.75.


B3 (Bulk Hours) is 2 - C3 (Pre-Paid Bulk Hours) is 5 - D3 (Overage Bulk Hours)should be 0.


This is where =IF(B3<=5,(B3),(B3-C3)) fails to work properly.


Good Luck.
 
so with cust1: if i paid for 5 hrs and the customer uses 16.75 hours, i still owe for 11.75 hours...correct?


but if i pay for 5 hrs and customer only uses 2, then i would have a credit worth 3 hrs...right?


what i dont understand in your formula is your ValueIfTrue argument (IF(B3<=5,B3)...why would you want this to represent the number of hours used?


maybe further explain why you think the answer should be 11.75 for cust2? also, where is this 'answer' supposed to be...d3?


also, instead of useing '5' in your condition, it may be better to use c3 as the number of hours pre-paid may vary per customer


may be a long shot, but is this what you're looking for: =IF(B3<=C3,B3-C3,B3-C3)
 
Agree somewhat with Jason. It's unclear where all your info is at, and what you are trying to get. Perhaps putting a small sample like

[pre]
Code: 
A   B   C
5   15  10
5   3   Credit 2
5   5   0
[/pre]
to illustrate what you want would help.
 
NARAYANK991 - that's perfect. Thank you very much.


jason - If the hours don't go over 5 in a month the Overage is 0. I have another formula that totals "Total Bulk Hours" (23) "Total Paid Bulk Hours" (15) "Bulk Hour Overage" (8)(for each quarter) & the "Bulk Hour Overage 2013". I went over hours in Jan, under in Feb & Mar so my overage for the year is 8.


I'll be sure to use this site again.
 
You must log in or register to reply here.
Back
Top