Need Help with Formula - PLZ!!

thescream80

Board Regular
Joined
Mar 28, 2014
Messages
94
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!!!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

thescream80

Board Regular
Joined
Mar 28, 2014
Messages
94
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

Board Regular
Joined
Jan 16, 2012
Messages
147
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

Board Regular
Joined
Mar 28, 2014
Messages
94

ADVERTISEMENT

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

Board Regular
Joined
Mar 28, 2014
Messages
94
Open Billable Word Total AFTER MINOpen Billable Balance
=AR8000-1500=AQ2*0.35+500
DATEProject NameTotal WordsTotal Words Billed
 

thescream80

Board Regular
Joined
Mar 28, 2014
Messages
94

ADVERTISEMENT

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

Board Regular
Joined
Jan 16, 2012
Messages
147
Bill
500​
Word Count
1500​
Extra per word
0.35​
WordsVarianceOverCharge
700​
-800​
FALSE​
500​
1600​
100​
TRUE​
535​
2000​
500​
TRUE​
675​
 

Richynero

Board Regular
Joined
Jan 16, 2012
Messages
147
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

Board Regular
Joined
Mar 28, 2014
Messages
94
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,869
Messages
5,544,779
Members
410,633
Latest member
ecronic
Top