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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,045
Office Version
  1. 365
Platform
  1. Windows
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)
 

Some videos you may like

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
Joined
Jan 16, 2012
Messages
147
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
Joined
Mar 28, 2014
Messages
94
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
    Screen Shot 2020-10-06 at 4.30.01 PM.png
    225.3 KB · Views: 2

thescream80

Board Regular
Joined
Mar 28, 2014
Messages
94
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
Joined
Jun 12, 2014
Messages
48,045
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, how about
+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
Joined
Mar 28, 2014
Messages
94
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
Ok, how about
+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
Joined
Jun 12, 2014
Messages
48,045
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,875
Messages
5,544,808
Members
410,635
Latest member
phoenix7771
Top