# Need Help with Formula - PLZ!!

#### thescream80

##### Board Regular
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

##### MrExcel MVP, Moderator
Is this what you need
+Fluff v2.xlsm
QRST
1Total WordsTotal Words BilledOpen BalanceOpen Word Count
210000100
350000600
4400001000
55010.350.351501
61003535.351601
Main
Cell Formulas
RangeFormula
R2:R6R2=MIN(Q2,MAX(0,SUM(Q\$2:Q2)-1500))*0.35
S2:S6S2=SUM(R\$2:R2)
T2:T6T2=SUM(Q\$2:Q2)

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Richynero

##### Board Regular
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

##### Board Regular
Is this what you need
+Fluff v2.xlsm
QRST
1Total WordsTotal Words BilledOpen BalanceOpen Word Count
210000100
350000600
4400001000
55010.350.351501
61003535.351601
Main
Cell Formulas
RangeFormula
R2:R6R2=MIN(Q2,MAX(0,SUM(Q\$2:Q2)-1500))*0.35
S2:S6S2=SUM(R\$2:R2)
T2:T6T2=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?

#### Attachments

• Screen Shot 2020-10-06 at 4.30.01 PM.png
225.3 KB · Views: 2

#### thescream80

##### Board Regular
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

##### MrExcel MVP, Moderator

+Fluff v2.xlsm
QRST
1Total WordsTotal Words BilledOpen BalanceOpen Word Count
210000100
350000600
4400001000
55010.350.351501
61003535.351601
7
8
9
10
11
Main
Cell Formulas
RangeFormula
R2:R10R2=IF(Q2="","",MIN(Q2,MAX(0,SUM(Q\$2:Q2)-1500))*0.35)
S2:S10S2=IF(R2="","",SUM(R\$2:R2))
T2:T6T2=SUM(Q\$2:Q2)

#### thescream80

##### Board Regular
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 WORDSTotal Words BilledOpen BalanceOpen Word Count
10000100
50000600
400001000
501.35.351501
10035.3535.351601
35.3570.71601
+Fluff v2.xlsm
QRST
1Total WordsTotal Words BilledOpen BalanceOpen Word Count
210000100
350000600
4400001000
55010.350.351501
61003535.351601
7
8
9
10
11
Main
Cell Formulas
RangeFormula
R2:R10R2=IF(Q2="","",MIN(Q2,MAX(0,SUM(Q\$2:Q2)-1500))*0.35)
S2:S10S2=IF(R2="","",SUM(R\$2:R2))
T2:T6T2=SUM(Q\$2:Q2)

THANK YOU! That worked great!!

Last edited by a moderator:

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
0
Views
86
Replies
2
Views
70
Replies
9
Views
233
Replies
1
Views
27
Replies
7
Views
111