Day & Charge Calculator Not Exactly Right?

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
171
Hello there, I am trying to get my calculator working properly. It's close but not quite there. It can correctly calculate the number of days even if it is a leap year and translate that into a figure to six decimal places. It cannot quite produce the correct figure and I can't work out why, hopefully it's obvious to one of you? I have added the correct details to the right of the calculator.

Cell Formulas
RangeFormula
F1F1=IF(ISBLANK(D7),"",(D4-D4*50%))
E3E3=IF(ISBLANK(D7),"","25% Discount is "&TEXT(D4*25%,"£#,##0.000000"&""))
F3F3=IF(ISBLANK(D7),"","50% Discount is "&TEXT(D4*50%,"£#,##0.000000"&""))
E4E4=IF(ISBLANK(D7),"","Charge is "&TEXT(D4*75%,"£#,##0.000000")&" for "&D9&" days")
F4F4=IF(ISBLANK(D7),"","Charge is "&TEXT(D4-D4*50%,"£#,##0.000000")&" for "&D9&" days")
E5E5=IF(ISBLANK(D7),"",(TEXT(D10,"£#,##0.000000")&" (25% discount is "&TEXT(D10*25%,"£#,##0.000000")&")"))
F5F5=IF(ISBLANK(D7),"",(TEXT(D10,"£#,##0.000000")&" (50% discount is "&TEXT(D10*50%,"£#,##0.000000")&")"))
E6E6=IF(ISBLANK(D7),"","Charge is "&TEXT(D10*75%,"£#,##0.00")&" for "&D9&" days")
F6F6=IF(ISBLANK(D7),"","Charge is "&TEXT(D10*50%,"£#,##0.00")&" for "&D9&" days")
G3G3=IF(D7="",""," (50% Discount is "&TEXT(F1,"£#,##0.000000")&") and (25% off 50% Discount is "&TEXT(F1*25%,"£#,##0.000000")&")")
G4G4=IF(ISBLANK(D7),"","Charge is "&TEXT(F1*25%*3,"£#,##0.000000")&" for "&D9&" days")
G5G5=IF(ISBLANK(D7),"",(TEXT(D10,"£#,##0.000000")&" (50% Discount is "&TEXT(D10*50%,"£#,##0.000000")&" and 25% off "&TEXT(D10*50%,"£#,##0.000000")&" is "&TEXT(D10/8,"£#,##0.000000")&")"))
G6G6=IF(ISBLANK(D7),"","Charge is "&TEXT(D10/8*3,"£#,##0.00")&" for "&D9&" days")
G7G7=IF(D12="","",TEXT(D9-D9*50%,"£#,##0.000000")&" for "&D14&" days")
D9D9=IF(ISBLANK(D7),"",D7-D6+NOT(C8))
D10D10=IF(ISBLANK(D7),"",SUMPRODUCT(D4/(365+(DAY(DATE(YEAR(CHOOSE({1,2},D6,D7-C8))+(MONTH(CHOOSE({1,2},D6,D7-C8))>3),2,29))=29))*LARGE(CHOOSE({1,2,3},((D7-C8)-D6)-((D7-C8)-MIN(D7-C8,DATE(YEAR(D6)+(MONTH(D6)>3),3,31))),(D7-C8)-MIN(D7-C8,DATE(YEAR(D6)+(MONTH(D6)>3),3,31)),0),{1,2})))
D11D11=D10
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E4Celldoes not contain a blank value textNO
F4Celldoes not contain a blank value textNO
G6Celldoes not contain a blank value textNO
F6Celldoes not contain a blank value textNO
G5Celldoes not contain a blank value textNO
F5Celldoes not contain a blank value textNO
E5Celldoes not contain a blank value textNO
G4Celldoes not contain a blank value textNO
G3Celldoes not contain a blank value textNO
F3Celldoes not contain a blank value textNO
E3Celldoes not contain a blank value textNO
D11Cellcontains a blank value textNO
C8Expression=C8textNO
E6Celldoes not contain a blank value textNO


Also, the inclusive or exclusive tickbox in D8 depends on validation on C8 being true or false. I want to lock the workbook so that the user can tab through the green squares but I cannot lock C8 or it stops that functionality from working. The problem is, if someone accidentally types something in C8 it will break the worksheet. Can someone please tell me the best way to work around this?

Thanks for your help.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
171
Also, the figures in row 4 & 5 do not reflect a change when the box is changed from exclusive to inclusive?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
Cross posted Day & Discount Calculator Equation & Cell Problem

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,333
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Your information is unclear and/or incomplete.
There are no Feb 29ths in the period; see C8.
There are 366 days in the period cited.
The daily charge is 1208.74/365

T202008c.xlsm
CDE
1
2
3
4Annual Charge1208.74
5
6Start Date1-Apr-20
7End Date1-Apr-21
80
9Number of Days3663.311616 daily charge
10Charge for Period1212.0516161212.051616
5d
Cell Formulas
RangeFormula
C8C8=SUMPRODUCT(--(TEXT(ROW(INDIRECT(D6&":"&D7)),"ddmm")="2902"))
D9D9=IF(ISBLANK(D7),"",D7-D6+1)
E9E9=D4/365
D10D10=D9/365*D4
E10E10=D9*E9


T202008c.xlsm
CDE
1
2
3
4Annual Charge1208.74
5
6Start Date1-Jan-20
7End Date31-Dec-20
81
9Number of Days3663.302568 daily charge
10Charge for Period1208.7400001208.74
5d
Cell Formulas
RangeFormula
C8C8=SUMPRODUCT(--(TEXT(ROW(INDIRECT(D6&":"&D7)),"ddmm")="2902"))
D9D9=IF(ISBLANK(D7),"",D7-D6+1)
E9E9=D4/(365+C8)
D10D10=D9/(365+C8)*D4
E10E10=D9*E9
 
Last edited:

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
171
Thanks for your replies, this question was posted here first and subsequently Day & Discount Calculator Equation & Cell Problem

Dave, I understand that there isn't an extra day in this year and it's confusing to you seeing that and thinking that is what I am checking. In the example it happens to be that I want to check that the calculator is working correctly by seeing it work out the daily charge for 365 days and 366 as I want to check out the functionality of the check box. It could have been any number of days but for testing I needed to see how it works for this period.

The calculator should determine the charge for the period. If the check box is changed to inclusive, it should add on an extra days charge.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,771
Messages
5,542,433
Members
410,552
Latest member
Yogesh977
Top