+ Reply to Thread
Results 1 to 12 of 12

Complex =IF(AND( formula alternative

  1. #1
    Registered User
    Join Date
    04-08-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    17

    Complex =IF(AND( formula alternative

    I have an Excel spreadsheet which contains the names of 8 types of fabrics. There are 4 colors of each fabric and each fabric is 10m meters in length. The price of each fabric is listed in a different cell in 50 cm increments starting from 1 m. So in essence there are 19 different prices for each fabric, and in total, 608 cells with different prices for all fabrics (8 types X 4 colors X 19 sizes = 608)
    I created a worksheet Where users input the Size of the fabric and the Type of the fabric as below.
    Cell A1 - Input the type of the fabric (8 options)
    Cell A2 - Input the color of the fabric (4 options)
    Cell A3 - Input the size of the fabric (19 options, 100-150cm, 200-250cm etc.)
    Cell A4 - This cell will display the price.


    I need a formula that will display the price of the fabric in Cell A4. Note that the prices can only be displayed in cell A4. No other cell in the worksheet can be used to show prices. The only option i can think of is to use an =IF(AND( statement but with 608 different prices that would take a day to write.

    Any ideas to save me the trouble of writing the =IF(AND( formula will be appreciated

  2. #2
    Registered User
    Join Date
    12-30-2018
    Location
    Romania
    MS-Off Ver
    Office 2016 Professional Plus
    Posts
    5

    Re: Complex =IF(AND( formula alternative

    You should consider uploading a demo file explaining what you are trying to achieve. It's a lot harder to read your message and properly understand what you need, let alone finding the best solution to achieve this.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Complex =IF(AND( formula alternative

    Can you please supply a copy of your workbook.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  4. #4
    Registered User
    Join Date
    04-08-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    17

    Re: Complex =IF(AND( formula alternative

    I uploaded the document. Thanks
    Attached Files Attached Files
    Last edited by vrachimis; 01-06-2019 at 03:25 PM.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Complex =IF(AND( formula alternative

    Thanks for the file, unfortunately it does not have any prices in it.
    I also suspect that the file is not really representative of your actual data.
    Can you please supply an accurate representation of your data, the prices do not need to be your actual price, just something that looks similar

  6. #6
    Registered User
    Join Date
    04-08-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    17

    Re: Complex =IF(AND( formula alternative

    Sorry about the prices. Yes i corrected the post so now you should be able to view the prices in the file.
    Yes this is a dummy file. The original file would take a long time to explain as it contains a lot more data.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Complex =IF(AND( formula alternative

    Ok, thanks for that, try
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    12-30-2018
    Location
    Romania
    MS-Off Ver
    Office 2016 Professional Plus
    Posts
    5

    Re: Complex =IF(AND( formula alternative

    You can also use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by radu.m; 01-06-2019 at 03:50 PM.

  9. #9
    Registered User
    Join Date
    04-08-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    17

    Re: Complex =IF(AND( formula alternative

    This one works. Perfect Thanks

  10. #10
    Registered User
    Join Date
    04-08-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    17

    Re: Complex =IF(AND( formula alternative

    Both work perfectly thanks!!!

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Complex =IF(AND( formula alternative

    You're welcome & thanks for the feedback

  12. #12
    Registered User
    Join Date
    12-30-2018
    Location
    Romania
    MS-Off Ver
    Office 2016 Professional Plus
    Posts
    5

    Re: Complex =IF(AND( formula alternative

    Glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Alternative to an IF formula
    By JonoRig in forum Excel General
    Replies: 8
    Last Post: 07-13-2017, 07:17 AM
  2. [SOLVED] Complex formula getting moer complex
    By Trebor777 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-02-2017, 01:39 PM
  3. 'Learning' alternative to complex if function
    By Excel4157 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2015, 02:56 PM
  4. [SOLVED] An alternative to the [IF] formula.
    By Aland2929 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2013, 08:12 AM
  5. Alternative Formula for multiples IFs (to make my formula shorter)
    By chico.corrales in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-07-2013, 12:37 AM
  6. alternative formula
    By gerard_gonzales33 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2012, 05:37 AM
  7. Complex If Formula or alternative formula?
    By helpneedednow in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 01:58 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1