Forum Discussion

Sarah801's avatar
Sarah801
Copper Contributor
Aug 13, 2024
Solved

Formula creation

I am new to this forum and only a part-time MS Excel user, so apologise for any terminology errors. I have the following spreadsheet (that I did not create, nor did I write all the current formulae ...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Aug 13, 2024

Sarah801 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Sarah801's avatar
Sarah801
Copper Contributor
Aug 14, 2024

HansVogelaar 

Hi, thanks for your reply, unfortunately (and a little surprisingly?), I am not able to attach a .xls or .xlsx document to this reply.  So I have uploaded the file to DropBox, please use this link:

https://www.dropbox.com/scl/fi/2utnhlhhik5f7fnoi78dd/MS-Forum-questions.xlsx?rlkey=4czsxfsoybk7ufn9kne55j2mq&st=oep3nw4y&dl=0

Thank you!

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 14, 2024

    Sarah801 

    Thanks.

    Question 1: enter the following formula in H2, then fill down.

     

    =IF(D2=0, 0, 0.85+MAX(D2-2, 0)*0.27)

     

    Question 2:

     

    You can simplify the formula in J2 to

     

    =IF(G2="RM", 3.95, IF(G2="TSRM", 4.95, 0))

     

    In K2:

     

    =IF(G3="APC", XLOOKUP(F3, {0,15,30,45,60}, {0,7.95,15.9,23.45,31.8}, "", 1), 0)

     

    It would be better to create a lookup list in the hidden sheet VL - see the attached version. I unhid the VL sheet to show how it works.

    • Sarah801's avatar
      Sarah801
      Copper Contributor
      Aug 14, 2024

      HansVogelaar 

      Thank you so much! The formula for Question 1 works perfectly, as does the simplified formula for J2.

      However, I can't seem to make either formula (I copied the formula out of your .xlsx file as well as trying the formula in your reply above) for Question 2, in the K2 cell, work, except to give me a 0 result if Delivery is RM. I worked out that the reference to G3 and F3 in the formula in your text above should be G2 and F2, but that still doesn't work. Using either formula with a Delivery method of APC, gives me a result of #NAME?
      Sorry if I am missing something obvious?

      Thank you also for pointing out there was a hidden sheet 🤦‍:female_sign: I realised the VLOOKUP formula must have been referencing something else, but had no idea what, or where, it was. Now that I know it is there, I can update it and look to use it 👍

Resources