Need Help with Formula - PLZ!!

[thescream80]

Hello,

I am trying to calculate a per word formula that I need help with.

AQ2= Open Word Total
AT2= Open Balance

AR2= Total Words
AS2= Total Words Billed

So what I need to calculate is after AQ2 (= Open Word Total) is above 1500words that each additional word is billed @ .35 each
There could be lots of entries each month and it will be zeroed out each month.

Total Words will be a manual entry and Open Word Count will be just a SUM but Any ideas on how to write a formula that shows the Total Words billed and Open Balances?

Example

Total Words. Total Words Billed. Open Balance Open Word Count
100 0 0 100
500 0 0 600
400 0 0 1000
501 .35 .35 1501
100 35.00 35.35 1601


Thank you for all of your help on this!!!

 

[thescream80]

I tried to do something like this

=IF(ISBLANK(AR121),"",IF(AQ2<1501,0,AR121*0.35))

But this did not work since once the AQ2 went about 1500 it would go back and start times all the previous entries by .35

 

[Richynero]

I would love to help but I really cant understand your question. Also, your table is not very clear. Perhaps use the insert table function to allow the table to be a bit more readable.

 

[thescream80]

Does this help?

 

[thescream80]

The thing I am trying to track here is its $500 a month for any words under 1500 - after 1500 words it is charged at .35 a word

 

[thescream80]

	Open Billable Word Total AFTER MIN		Open Billable Balance
	=AR8000-1500		=AQ2*0.35+500
DATE	Project Name	Total Words	Total Words Billed

 

[thescream80]

I would love to help but I really cant understand your question. Also, your table is not very clear. Perhaps use the insert table function to allow the table to be a bit more readable.

Let me know if that helps!

 

[Richynero]

Bill	500​
Word Count	1500​
Extra per word	0.35​
	Words	Variance	Over	Charge
	700​	-800​	FALSE​	500​
	1600​	100​	TRUE​	535​
	2000​	500​	TRUE​	675​

 

[Richynero]

Where in the variance column =B6-$B$2
In the over column C6>0
In the charge column =IF(D6,($B$3*C6)+$B$1,$B$1)

Where Bill is in cell A1. Hope that answers the question??

 

[thescream80]

I cant get this to work for me for some reason. To make sure I am reading this right B6 (1600) correct?

Also is there a way to not show anything if column B is blank?