Scribd Logo
Upload

Welcome to Scribd!

  • 759 views

    Practice of IF

    Uploaded by

    Jannat Rajput
    The third document describes creating an IF function to calculate discounts applied to orders based on quantity, with 10% discount for orders over 2,000 and 1% for those under 2,000.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    Practice of IF

    Uploaded by

    Jannat Rajput
    759 views18 pages
    The third document describes creating an IF function to calculate discounts applied to orders based on quantity, with 10% discount for orders over 2,000 and 1% for those under 2,000.

    Original Title

    Practice of IF.xlsx

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The third document describes creating an IF function to calculate discounts applied to orders based on quantity, with 10% discount for orders over 2,000 and 1% for those under 2,000.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    759 views18 pages

    Practice of IF

    Uploaded by

    Jannat Rajput
    The third document describes creating an IF function to calculate discounts applied to orders based on quantity, with 10% discount for orders over 2,000 and 1% for those under 2,000.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    of 18

    Q1.

    Create a nested =IF formula in cell E2 to show the action to take based on the following:
    Q1.If the number of yellow cards is greater than or equal to 30, then the fine is 10% of their salary;
    If the number of yellow cards is greater than or equal to 10, then the fine is 2% of their salary;
    If the number of yellow cards is any less than 10 then insert the text No Action in the cell.

    Footballer Salary p.m. Yellow Cards Red Cards Action to take


    Wayne Rooney £85,000 8 0 No Action
    Robin van Persie £87,500 4 0 No Action
    Lionel Messi £92,300 18 2 £ 1,846.00
    Cristiano Ronaldo £98,600 25 6 £ 1,972.00
    Fernando Torres £74,500 7 0 No Action
    Gareth Bale £38,000 3 0 No Action
    David Silva £46,400 5 1 No Action
    Frank Lampard £64,500 14 2 £ 1,290.00
    Carlos Tevez £78,300 58 19 £ 7,830.00
    Didier Drogba £66,350 12 2 £ 1,327.00
    heir salary;

    Yellow high 10%


    Yellow low 2%

    £9,230
    Q2.Create an If function to calculate whether each movie was a flop or a success.  Use the following criteria:
    If the profit was less than 100,000,000 then the movie is a flop
    Otherwise the movie is a success

    Movie Budget ($) World Gross ($) Profit


    Spider-Man 3 258,000,000 887,436,184 629,436,184
    King Kong (2005) 207,000,000 553,080,025 346,080,025
    Superman Returns 204,000,000 391,081,192 187,081,192
    Spider-Man 2 200,000,000 784,024,485 584,024,485
    Titanic 200,000,000 1,835,400,000 1,635,400,000
    Chronicles of Narnia, The 180,000,000 748,806,957 568,806,957
    Wild Wild West 175,000,000 217,700,000 42,700,000
    Evan Almighty 175,000,000 120,698,890 -54,301,110
    Waterworld 175,000,000 264,246,220 89,246,220
    Terminator 3: Rise of the Mac 170,000,000 433,058,296 263,058,296
    Polar Express, The 170,000,000 296,596,043 126,596,043
    Van Helsing 170,000,000 300,150,546 130,150,546
    Shrek the Third 160,000,000 733,012,359 573,012,359
    Poseidon 160,000,000 181,674,817 21,674,817
    Alexander 155,000,000 167,297,191 12,297,191
    Pearl Harbor 151,500,000 450,500,000 299,000,000
    Harry Potter and the Goblet of 150,000,000 892,213,036 742,213,036
    Harry Potter and the Order of 150,000,000 822,828,538 672,828,538
    Mission: Impossible III 150,000,000 397,501,348 247,501,348
    Troy 150,000,000 497,298,577 347,298,577
    s.  Use the following criteria:

    Flop or Not?
    SUCCESS
    SUCCESS
    SUCCESS
    SUCCESS
    SUCCESS
    SUCCESS
    FLOP
    FLOP
    FLOP
    SUCCESS
    SUCCESS
    SUCCESS
    SUCCESS
    FLOP
    FLOP
    SUCCESS
    SUCCESS
    SUCCESS
    SUCCESS
    SUCCESS
    Q3.In cell D4, create an =IF function to calculate Discount Applied.  Use the following information to help you:
    If the order quantity is greater than 2000, the discount is 10%
    Anything less, the discount is 1%

    Order Discount Final Order


    Company Order Cost
    Quantity Applied Cost
    CDC Industries Ltd 1500 £33,525.00 £335.25 £33,189.75
    Fox & Co 2300 £51,405.00 £5,140.50 £46,264.50
    FDL Printers Ltd 450 £10,057.50 £100.58 £9,956.93
    Myers and Gough LLB 900 £20,115.00 £201.15 £19,913.85
    Abco plc 1300 £29,055.00 £290.55 £28,764.45
    Daniels Healthcare 800 £17,880.00 £178.80 £17,701.20
    Pets R Us 3600 £80,460.00 £8,046.00 £72,414.00
    Abelmans 350 £7,822.50 £78.23 £7,744.28
    TOTAL: 11200 £250,320.00 £250,320.00

    Price £22.35
    Threshold 2000
    High Discount 10%
    Low Discount 1%
    nformation to help you:
    Q4.In cell C4, create an =IF function to calculate the number of club card vouchers earned.  Use the following inform
    If the amount is greater than £95, you get 1 voucher per £4 spent
    Anything less you only get 1 voucher for every £10 spent

    Pesko Club Card Vouchers Pesko Club Card Vouchers


    Date Amount Club card Date Amount Club card
    Vouchers Vouchers
    Earned Earned

    15-Jan £112.66 28 15-Jan £112.66 28


    21-Jan £17.54 2 21-Jan £17.54 2
    4-Mar £96.87 24 4-Mar £96.87 24
    11-May £32.78 3 11-May £32.78 3
    29-May £56.38 6 29-May £56.38 6
    20-Jun £62.94 6 20-Jun £62.94 6
    2-Aug £74.89 7 2-Aug £74.89 7
    18-Aug £13.45 1 18-Aug £13.45 1
    7-Sep £73.63 7 7-Sep £73.63 7
    3-Oct £93.86 9 3-Oct £93.86 9
    29-Oct £52.11 5 29-Oct £52.11 5
    8-Dec £143.67 36 8-Dec £143.67 36

    Voucher Thres £95.00 Voucher Th £95.00

    £4 = 1 voucher £4 = 1
    voucher
    0.25 whole vouchers 0.25 whole
    only
    vouchers only
    High Vouchers High Vouch
    £10 = 1
    £10 = 1 voucher voucher
    0.1 whole vouchers 0.1 whole
    only
    vouchers only
    Low Vouchers Low Vouch
    rned.  Use the following information to help you:
    Car Parking Charges

    No. Hrs Parking


    Car Reg
    Parked Charge
    DA12 NEJ 6 If park >6 hours, pay £3.50 pr hr
    MA16 BVW 12 Anything less pay £1 pr hr
    DD11 SFD 8
    MA14 NHG 11 Threshold 6
    YK14 BHH 5 Long Hour Fee £3.50
    DY15 FLB 3 Short Hour Fee £1.00
    MM12 SWL 12
    MA16 GKW 7
    FS12 DSD 1
    DA11 SBM 6
    Create an If function to calculate a total order value based on the following criteria:
    If less than 1000 units are ordered each unit costs the normal price
    Otherwise each unit costs the discounted price

    Widget Sales Widget Unit Prices


    Order No. Customer Quantity Order Total Normal Discount
    Ord-001 Donald Duck 950 £1.00 £0.90
    Ord-002 Mickey Mouse 1090
    Ord-003 Bugs Bunny 500
    Ord-004 Porky Pig 650
    Ord-005 Daffy Duck 1200
    Ord-006 Goofy 800
    Create an If function to calculate a bonus for each player based on the following criteria:
    If a players Goals Scored meets or exceeds his Goals Target he receives a bonus equal to £1000 for each go
    Otherwise he receives an encouraging message

    Target Scores
    Name Goals Target Goals Scored Bonus
    Rooney 15 15
    Drogba 20 18
    Ronaldo 15 16
    Owen 20 18
    Torres 18 14
    Essien 12 8
    Viduka 15 16
    al to £1000 for each goal he has scored
    reate a nested If function to describe the Teletubbies coffee drinking habits based on the following criteria:
    0 cups = Tea drinker
    1-5 cups = Normal
    More than 5 cups = Caffeine friend
    Try modifying the If function so that if Cups of coffee is a negative number you see an appropriate

    Name Cups of coffee Description


    Dipsy 0
    Laa-Laa 6
    Po 3
    Tinky-Winky 12
    The Nu-Nu 4
    The Clumsy -1
    n the following criteria:

    ou see an appropriate error message.


    Project Penalty
    Days
    Project Name Cost Payment
    (£mill) Overdue (£ mill)
    Ambleside Disabled Toilet 0.65 14
    Bath Cycle Path 2.3 -22
    Buxton Brine Baths Restorations 14.8 0
    Chester Cathedral Renovations 7.3 12
    Halesowen Lido Works 3.6 88
    Skegness Promenade Improvements 2.6 19
    Warrington Cricket Pitch Drainage 0.8 -56
    York Zero Carbon Housing Project 25.4 2

    If project >50 days overdue, 15% of project cost (Project cost in cell B2 * 15%)
    If project >0 days overdue, 5% of project cost (Project cost in cell B2 * 5%)
    Anything less "On target"
    High Days 50
    Low Days 0
    High Penalty 15%
    Low Penalty 5%
    Cheeky Charlie's School Tuckbox Sales - Class 4A Sales

    Cost
    Bulk Buy Class
    Item Price Quantity Before
    Discount Cost
    Discount
    Mars Bars £1.00 8 £8.00 £8.00
    Twix £1.00 5 £5.00 £5.00
    Bounty Bar £1.00 6 £6.00 £6.00
    Gazillions Tub £1.50 12 £18.00 £18.00
    Maltesers £1.00 1 £1.00 £1.00
    Walkers Crisps £0.75 15 £11.25 £11.25
    Salted Peanuts £1.50 8 £12.00 £12.00
    Chewing Gum £0.75 5 £3.75 £3.75
    Nutella Bar £2.00 4 £8.00 £8.00
    Polo Mints £0.50 4 £2.00 £2.00
    Muesli Bar £2.00 1 £2.00 £2.00
    Maths Test Answers £5.00 25 £125.00 £125.00
    CLASS TOTALS £18.00 94 £0.00 £202.00
    Discounts for Bulk Buy

    If buy >= 20 get 10% discount


    If buy >=10 get 5% discount
    Anything less no discount

    Threshold1 20
    Threshold2 10
    High Discount 10%
    Low Discount 5%

    You might also like