Spreadsheet Sales Report

BY IN ITGS - Database & Spreadsheet Comments Off on Spreadsheet Sales Report

Sales Report

A: Disc 10%
B: 15%
C: 20%

Determine the formula to calculate sales discount depend on item
=IF(C2=”A”,0.1,IF(C2=”B”,0.15,0.2))

Fomula IF is needed more than one, IF ONLY the condition have more than two values
Checking the string/text value need to put inside quote, while checking the number value don’t
If the formula has error, please check the syntax and the sign

Using an absolute cell address for the discount, reconstruct the formula above
=IF(C2=”A”,$B$10,IF(C2=”B”,$B$11,$B$12))
Benefit:
If there is a change we don’t need to reconstruct the formula, just simply change the value on the address instead

Determine the tax formula for item C only! (10% tax)
=IF(C2=”C”,0.1,0)

Determine group column’s formula with the folowing information
A: Group Stationery
B: Computer
C: Photocopier

=IF(C2=”A”,”Stationery”,IF(C2=”B”,”Computer”,”Photocopier”))

Determine the formula to calculate the total of the amount that customer should pay! (sales price*qty-disc+tax)
=((F2*E2)-(F2*E2*G2)+(F2*E2*H2)

Determine the formula to calculate the total of sales
=SUM(I2:I8)

Reconstruct the formula for calculating sub total, if there is no tax column.
(with the same condition, which is there will be 10% tax for item C)
=((F2*E2)-(F2*E2*G2)+(IF(C2=”C”,(F2*E2*0.1),0))




Comments are closed.