Hi Excel Guru's
I am getting myself beyond confused with an excel formula and would really appreciate some help. I am using Excel 365.
I have a sheet I use to track the cost of exams. In column F I have a IF statement to determine the cost of the exam based on the exam type. I now also need to the formula to look at column G to change the price depending if is a voucher, courier or self Print.
In cell F6, I would like a formula that does something like this:
If cell D6 equals ITIL 4 Foundation, then the base cost would be E19, and then if G6 equals voucher then the price would be G19 ((or if G6 equals Courier then the price would be F19))
Is this possible? I would really appreciate the help as I have confused myself so much nothing is making sense now.
I hope I have added the Mini Sheet correctly.
Regards
Deb
I am getting myself beyond confused with an excel formula and would really appreciate some help. I am using Excel 365.
I have a sheet I use to track the cost of exams. In column F I have a IF statement to determine the cost of the exam based on the exam type. I now also need to the formula to look at column G to change the price depending if is a voucher, courier or self Print.
In cell F6, I would like a formula that does something like this:
If cell D6 equals ITIL 4 Foundation, then the base cost would be E19, and then if G6 equals voucher then the price would be G19 ((or if G6 equals Courier then the price would be F19))
Is this possible? I would really appreciate the help as I have confused myself so much nothing is making sense now.
I hope I have added the Mini Sheet correctly.
Regards
Deb
| Tracking Sheet test formula.xlsx | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | L | M | Q | R | S | T | U | |||||||||
| 1 | COURSE DETAILS | Invoice | SUPPLIER INVOICES | ||||||||||||||||||||
| 2 | Date Ordered | Semdet | Exam Date | Exam Type | No. Ordered | Total cost of order ($) | Deliver Method | testing column | rec'd | Total No Exams Used | Rate | No. Exams inv'd | Total Due | Invoice No. | Discount | ||||||||
| 3 | FALSE | FALSE | 0 | $0.00 | |||||||||||||||||||
| 4 | FALSE | FALSE | 0 | $0.00 | |||||||||||||||||||
| 5 | FALSE | FALSE | 0 | $0.00 | |||||||||||||||||||
| 6 | 28/4/21 | YVF-VIR-001 | ITIL 4 Foundation | 6 | $1,501.20 | Voucher | 280 | 5/5/21 | 6 | $280.00 | 6 | $1,680.00 | 29580 | ||||||||||
| 7 | 27/4/21 | YDD-VIR-008 | ITIL 4 Foundation | 10 | $2,800.00 | Voucher | 280 | 28/4/21 | 10 | $280.00 | 10 | $2,800.00 | 28639 | ||||||||||
| 8 | 19/4/21 | IT4-VIR-00J | ITIL 4 Foundation | 16 | $4,003.20 | Voucher | 280 | 27/4/21 | 16 | $280.00 | 16 | $4,480.00 | 26412 | ||||||||||
| 9 | 9/4/21 | YRN-SYD-001 | 16/04/21 | ITIL 4 Foundation | 10 | $2,502.00 | Self Print | 250.2 | 20/4/21 | 6 | $250.20 | 6 | $1,501.20 | 26320 | |||||||||
| 10 | 7/4/21 | IT4-BRI-00E | ITIL 4 Foundation | 10 | $2,502.00 | COURIER | 250.2 | 26/4/21 | 6 | $250.20 | 6 | $1,537.20 | 27977 | ||||||||||
| 11 | 29/3/21 | IT4-VIN-003 | ITIl 4 MPT | 3 | $1,155.60 | Voucher | 30/3/21 | 3 | $280.00 | 3 | $840.00 | 21373 | |||||||||||
| 12 | 25/3/21 | YNK-VIR-001 | ITIL 4 Foundation | 14 | $3,502.80 | Voucher | 26/3/21 | 14 | $280.00 | 14 | $3,920.00 | 20265 | |||||||||||
| 13 | 22/3/21 | YDR-VIR-003 | ITIL 4 Foundation | 13 | $3,252.60 | Voucher | 23/3/21 | 13 | $280.00 | 13 | $3,640.00 | 19307 | |||||||||||
| 14 | 27/1/21 | IT4-VIN-003 | ITIL 4 Foundation | 5 | $1,251.00 | Voucher | 7/10/14 | 5 | $280.00 | 5 | $1,400.00 | 5394 | |||||||||||
| 15 | 18/1/21 | IT4-VIR-00F | ITIL 4 Foundation | 7 | $1,751.40 | Voucher | 19/1/21 | 7 | $280.00 | 7 | $1,960.00 | 3242 | |||||||||||
| 16 | |||||||||||||||||||||||
| 17 | Resit Cost | ||||||||||||||||||||||
| 18 | excl GST | Base Cost | Paper | Web | AU + GST | ||||||||||||||||||
| 19 | ITIL Foundation | $244.00 | $250.20 | $280.00 | $280.00 | ||||||||||||||||||
| 20 | ITIl 4 MPT | $379.00 | $385.20 | $415.00 | $415.00 | ||||||||||||||||||
| 21 | ITIl 4 Specialist | $379.00 | $385.20 | $415.00 | $415.00 | ||||||||||||||||||
| 22 | ITIL Practitioner | $379.00 | $385.20 | $415.00 | $415.00 | ||||||||||||||||||
Exam | |||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| R3:R15 | R3 | =M3 |
| S3:S5,S9:S10 | S3 | =SUM(Q3*R3+J3)-U3 |
| S6:S7,S14:S15 | S6 | =SUM(Q6*R6)-U6 |
| S8,S11:S13 | S8 | =SUM(Q8*R8) |
| Q3:Q5,Q9:Q10 | Q3 | =IF(D3="ITIL 4 Foundation",$F$19,IF(D3="ITIL 4 MBT",$F$20,IF(D3="ITIL4 Specialist",$F$21,IF(D3="ITIL Practitioner",$F$22)))) |
| H6:H10 | H6 | =IF(G6="Voucher",280,250.2) |
| F3:F6,F8:F10,F12:F15 | F3 | =IF(D3="ITIL 4 Foundation",$F$19*E3,IF(D3="ITIL 4 MBT",$F$20*E3,IF(D3="ITIL4 Specialist",$F$21*E3,IF(D3="ITIL Practitioner",$F$22*E3)))) |
| F7 | F7 | =E7*280 |
| F11 | F11 | =IF(D11="ITIL 4 Foundation",$F$19*E11,IF(D11="ITIL 4 MPT",$F$20*E11,IF(D11="ITIL4 Specialist",$F$21*E11,IF(D11="ITIL Practitioner",$F$22*E11)))) |
| F19:F22 | F19 | =E19+6.2 |
| G19:G22 | G19 | =E19+36 |
| H19:H22 | H19 | =G19 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| E19 | Cell Value | >TODAY() | text | NO |
| E19 | Cell Value | >(TODAY) | text | NO |
| E20:E22 | Cell Value | >TODAY() | text | NO |
| E20:E22 | Cell Value | >(TODAY) | text | NO |
| F19:F22 | Cell Value | >TODAY() | text | NO |
| F19:F22 | Cell Value | >(TODAY) | text | NO |
| H20:I20 | Cell Value | ="Voucher" | text | NO |
| C14:C15 | Cell Value | >TODAY() | text | NO |
| C14:C15 | Cell Value | >(TODAY) | text | NO |
| C1,F17,C23:C1048576,C15:C16,C3:C13 | Cell Value | >TODAY() | text | NO |
| H21:I22 | Cell Value | ="Voucher" | text | NO |
| C1,F17,C23:C1048576,C15:C16,C3:C13 | Cell Value | >(TODAY) | text | NO |
| G23:H1048576,H17:I19,G1:H16 | Cell Value | ="Voucher" | text | NO |
