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.
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.
| Discount Calculator (Export).xlsm | |||||||
|---|---|---|---|---|---|---|---|
| C | D | E | F | G | |||
| 1 | £604.370000 | ||||||
| 2 | |||||||
| 3 | 25% Discount is £302.185000 | 50% Discount is £604.370000 | (50% Discount is £604.370000) and (25% off 50% Discount is £151.092500) | ||||
| 4 | Annual Charge | £1,208.74 | Charge is £906.555000 for 365 days | Charge is £604.370000 for 365 days | Charge is £453.277500 for 365 days | ||
| 5 | £1,205.428384 (25% discount is £301.357096) | £1,205.428384 (50% discount is £602.714192) | £1,205.428384 (50% Discount is £602.714192 and 25% off £602.714192 is £150.678548) | ||||
| 6 | Start Date | 01 04 2020 | Charge is £904.07 for 365 days | Charge is £602.71 for 365 days | Charge is £452.04 for 365 days | ||
| 7 | End Date | 01 04 2021 | |||||
| 8 | Exclusive End Date | ||||||
| 9 | Number of Days | 365 | Daily charge is £3.302568 | ||||
| 10 | Charge for Period | £1,205.428384 | Total should be £1,205.437432 to six decimal places | ||||
| 11 | £1,205.43 | Rounded total should be £1,205.44 | |||||
Calculator | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F1 | F1 | =IF(ISBLANK(D7),"",(D4-D4*50%)) |
| E3 | E3 | =IF(ISBLANK(D7),"","25% Discount is "&TEXT(D4*25%,"£#,##0.000000"&"")) |
| F3 | F3 | =IF(ISBLANK(D7),"","50% Discount is "&TEXT(D4*50%,"£#,##0.000000"&"")) |
| E4 | E4 | =IF(ISBLANK(D7),"","Charge is "&TEXT(D4*75%,"£#,##0.000000")&" for "&D9&" days") |
| F4 | F4 | =IF(ISBLANK(D7),"","Charge is "&TEXT(D4-D4*50%,"£#,##0.000000")&" for "&D9&" days") |
| E5 | E5 | =IF(ISBLANK(D7),"",(TEXT(D10,"£#,##0.000000")&" (25% discount is "&TEXT(D10*25%,"£#,##0.000000")&")")) |
| F5 | F5 | =IF(ISBLANK(D7),"",(TEXT(D10,"£#,##0.000000")&" (50% discount is "&TEXT(D10*50%,"£#,##0.000000")&")")) |
| E6 | E6 | =IF(ISBLANK(D7),"","Charge is "&TEXT(D10*75%,"£#,##0.00")&" for "&D9&" days") |
| F6 | F6 | =IF(ISBLANK(D7),"","Charge is "&TEXT(D10*50%,"£#,##0.00")&" for "&D9&" days") |
| G3 | G3 | =IF(D7="",""," (50% Discount is "&TEXT(F1,"£#,##0.000000")&") and (25% off 50% Discount is "&TEXT(F1*25%,"£#,##0.000000")&")") |
| G4 | G4 | =IF(ISBLANK(D7),"","Charge is "&TEXT(F1*25%*3,"£#,##0.000000")&" for "&D9&" days") |
| G5 | G5 | =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")&")")) |
| G6 | G6 | =IF(ISBLANK(D7),"","Charge is "&TEXT(D10/8*3,"£#,##0.00")&" for "&D9&" days") |
| G7 | G7 | =IF(D12="","",TEXT(D9-D9*50%,"£#,##0.000000")&" for "&D14&" days") |
| D9 | D9 | =IF(ISBLANK(D7),"",D7-D6+NOT(C8)) |
| D10 | D10 | =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}))) |
| D11 | D11 | =D10 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| E4 | Cell | does not contain a blank value | text | NO |
| F4 | Cell | does not contain a blank value | text | NO |
| G6 | Cell | does not contain a blank value | text | NO |
| F6 | Cell | does not contain a blank value | text | NO |
| G5 | Cell | does not contain a blank value | text | NO |
| F5 | Cell | does not contain a blank value | text | NO |
| E5 | Cell | does not contain a blank value | text | NO |
| G4 | Cell | does not contain a blank value | text | NO |
| G3 | Cell | does not contain a blank value | text | NO |
| F3 | Cell | does not contain a blank value | text | NO |
| E3 | Cell | does not contain a blank value | text | NO |
| D11 | Cell | contains a blank value | text | NO |
| C8 | Expression | =C8 | text | NO |
| E6 | Cell | does not contain a blank value | text | NO |
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.
