Using I5 as your anchor cell (which means you must use offsetfrom I5 for all the questions below and make use of the With..endWith construct). Finding the number of rows of items you have mightbe helpful in this case.
(i) insert a title "Subtotal" in a cell of column J that is tworows below the last entry in that column (i.e. there must be a gapof 2 rows).
(ii) insert a title "Discount" in the cell below the cell inwhich you wrote "Subtotal"
(iii) insert a title "Total" in the cell below the cell in whichyou wrote "Discount"
(iv) in column K, in the cells below "Total Cost", calculate theCost of Units using the following formula in R1C1 format:
Cost of Units = # of Units * Price per Unit
The price per unit must be picked from the data in columns C,Dand E based on the number of units of each type the customer wishesto buy.
(You can use a simple if‐else statement for this and no need tointroduce loops. At this point, we are not bothered about any newtype of machine the user enters and is concerned only aboutcalculating for the 3 given machines, so do not worry about makingthe code very generic.)
(v) Format the values calculated in (iv) above such that itshows atleast one digit to the left of the decimal and always showstwo digits to the right of the decimal point.
(vi) The cell to the right of "Subtotal" must show the sum ofthe values in the cells below "Total Cost". Use formula in R1C1format. Format this value such that it has a $ sign and showsatleast one digit to the left of the decimal and always shows twodigits to the right of the decimal point. Also, store this value toa double variable called dblSubtotal. (Hint: Read the 2nd tip givenat the end of the problem.)
(vii) The cell to the right of "Discount" must display the valuein cell E2 (absolute reference) if thesubtotal value is greaterthan $65,000, else it must show zero. Store this value to a doublevariable called dblDiscount. Format this value such that it showsin percentage when displayed on your sheet with atleast one digiteach on both sides of the decimal (but don't format when you storeit to the double variable)
.
(viii) The cell to the right of "Total" must calculate anddisplay the value of the formula:
Total = (1 ‐ Discount) * Subtotal
You can make use of the double variables defined above in thisformula. Format this value such that it has a $ sign and showsatleast one digit to the left of the decimal and always shows twodigits to the right of the decimal point.
Here's what I have so far
- Can U Set This Up In Vba Excel Using I5 As Your Anchor Cell Which Means You Must Use Offset From I5 For All The Questio 1 (93.61 KiB) Viewed 47 times