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!!!

 

[Fluff]

Is this what you need

+Fluff v2.xlsm
	Q	R	S	T
1	Total Words	Total Words Billed	Open Balance	Open Word Count
2	100	0	0	100
3	500	0	0	600
4	400	0	0	1000
5	501	0.35	0.35	1501
6	100	35	35.35	1601
Main

Cell Formulas
Range	Formula
R2:R6	R2	=MIN(Q2,MAX(0,SUM(Q$2:Q2)-1500))*0.35
S2:S6	S2	=SUM(R$2:R2)
T2:T6	T2	=SUM(Q$2:Q2)

 

[Richynero]

B6 is 1600. Column B has a lot of numbers which one are you saying would blank. Would help if you were explicit as possible as to leave no areas of ambiguity.

 

[thescream80]

Is this what you need

+Fluff v2.xlsm
	Q	R	S	T
1	Total Words	Total Words Billed	Open Balance	Open Word Count
2	100	0	0	100
3	500	0	0	600
4	400	0	0	1000
5	501	0.35	0.35	1501
6	100	35	35.35	1601
Main

Cell Formulas
Range	Formula
R2:R6	R2	=MIN(Q2,MAX(0,SUM(Q$2:Q2)-1500))*0.35
S2:S6	S2	=SUM(R$2:R2)
T2:T6	T2	=SUM(Q$2:Q2)

Hi Fluff! That is really close to what I need! 2 Things I am seeing
1. As you can see in the screenshot when I extend the formula out the Open Balance seems to be going higher when the total words is blank.
2. Is there a way if say if the Total Words is blank it would not display anything is other columns?

 

[thescream80]

B6 is 1600. Column B has a lot of numbers which one are you saying would blank. Would help if you were explicit as possible as to leave no areas of ambiguity.

Sorry about that Richynero! I really appriciate your help and applogize for not being clear. I am still new at all of this and dont do it enough to gain any sort of clarity to know exactly what I need. I feel we are very close now though.

 

[Fluff]

Ok, how about

+Fluff v2.xlsm
	Q	R	S	T
1	Total Words	Total Words Billed	Open Balance	Open Word Count
2	100	0	0	100
3	500	0	0	600
4	400	0	0	1000
5	501	0.35	0.35	1501
6	100	35	35.35	1601
7
8
9
10
11
Main

Cell Formulas
Range	Formula
R2:R10	R2	=IF(Q2="","",MIN(Q2,MAX(0,SUM(Q$2:Q2)-1500))*0.35)
S2:S10	S2	=IF(R2="","",SUM(R$2:R2))
T2:T6	T2	=SUM(Q$2:Q2)

 

[thescream80]

Hi Fluff! That is really close to what I need! 2 Things I am seeing
1. As you can see in the screenshot when I extend the formula out the Open Balance seems to be going higher when the total words is blank.
2. Is there a way if say if the Total Words is blank it would not display anything is other columns?

Do this is the error I am getting - When I extended the range beyond 5 entries in Column Under Totals Words

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.35	35.35	1601
	35.35	70.7	1601

Ok, how about

+Fluff v2.xlsm
	Q	R	S	T
1	Total Words	Total Words Billed	Open Balance	Open Word Count
2	100	0	0	100
3	500	0	0	600
4	400	0	0	1000
5	501	0.35	0.35	1501
6	100	35	35.35	1601
7
8
9
10
11
Main

Cell Formulas
Range	Formula
R2:R10	R2	=IF(Q2="","",MIN(Q2,MAX(0,SUM(Q$2:Q2)-1500))*0.35)
S2:S10	S2	=IF(R2="","",SUM(R$2:R2))
T2:T6	T2	=SUM(Q$2:Q2)

THANK YOU! That worked great!!

 

[Fluff]

You're welcome & thanks for the feedback.