Question:

Applying a formula to a worksheet with 2000+ cells?

by  |  earlier

0 LIKES UnLike

Hi, everyone, I'm taking a Microsoft Excel 2007 class on line and need help applying the formula to a worksheet that has 2000+ cells. I'm trying to calculate the price of an item by the quantity sold. I'm really stuck and need some help desperately. If you need more info, please say so in your answer......please respond; this project is due TODAY. Thanks in advance.

 Tags:

   Report

1 ANSWERS


  1. If the price structure is very simple, say only 3 tiers of price, just use IF statement

    e.g. 1-10 nos. is $10, 11-20 nos. is $9.5, >20 nos. is $9

    cell A1 is for quantity, in B1 to show price

    =IF( A1>20, 9, IF(A1<=10, 10, 9.5))

    If the price structure is complex,

    e.g.

    1-59 nos. -> $10

    60-69 nos. -> $9.5

    70-74 nos. -> $9

    75-84 nos. ->$8.5

    85-89 nos. -> $8

    90 or above nos. -> $7.5

    Use VLOOKUP function instead

    General syntax: VLOOKUP(<LOOKUP VALUE>, <LOOKUP RANGE>, <COLUMN FROM LEFT IN LOOKUP RANGE>, <EXACT MATCH?>)

    If the function find the match, it will return the corresponding value, if not, returns #N/A

    To deal with the above price schedule, build a table 1st

    then a table like

    A1 = 0 B1 = 10

    A2 = 60 B2 = 9.5

    A3 = 70 B3 = 9

    A4 = 75 B4 = 8.5

    A5 = 85 B5 = 8

    A1 = 90 B6 = 7.5

    in D1 and D2 we input the quantity 91 and 74 respectively

    in E1 put =VLOOKUP(D1,$A$1:$B$6,2,TRUE) then E1 will return 7.5 as the price

    copy the formula to E2 then it will return 9 as the price

    Note: $ signs is need to fix table location as formula is copying down column E

    parameter 2 in formula is the return value(price)

    parameter True in formula is for closest(equal or less) match.

    Hope this helps.

Question Stats

Latest activity: earlier.
This question has 1 answers.

BECOME A GUIDE

Share your knowledge and help people by answering questions.