Help With Excel

1

Hi There Everyone, I am semi-new to programming (challenging myself to do a hundred days of code!). I am very new to trying to make Excel work nicer for me. I am currently working on a side project to build a proposal sheet for a friend of mine who owns a plumbing business. The problem i am trying to solve is assigning a Word to an IF statement and returning with a numerical value. I figured out how to do this last night but then ran into the next issue, the size of my formula can only be 255 characters, and i have over 350 different word input, value output senerios. Making it where no matter what option is entered into the spreadsheet, the associated value would be given.

I am using the following

=IF(B3=“0.5 Copper Joint”,0.20,IF(B3="0.5 Copper Valve”,0.40,IF(B3=”0.5 Copper Tee”,0.60,IF(B3=”0.5 Copper ELL”,0.40,IF(B3=”0.5 Copper LF Pipe”,0.04,IF(B3=”0.75 Copper Joint”,0.21,IF(B3=”0.75 Copper Valve”,0.42,IF(B3=”0.75 Copper Tee”,0.63,IF(B3=”0.75 Copper ELL”,0.42,IF(B3=”0.75 Copper LF Pipe”,0.04))))))))))

If i shorten this code up it works like a charm, and it would work if excel allowed longer formulas. I was thinking that there has to be a better way than to write out 350 different IF,THEN statements. Is there? How do i get Excel to allow me to use a 2500 word formula rather than 250? Am i overthinking this? Is this even possible to do this in Excel? If not, what language should I focus on to create this spreadsheet? I want to learn how to create this if someone could simply provide direction to complete my ultimate goal, I am perfectly okay with scrapping the excel format to switch to a more suitable format that will allow me to create the overall spreadsheet to perfection. I am here to learn.

I appreciate your time and help,

Thank You.

2

I don’t know if anyone here will specifically have experience in excel to be quiet honest. That’s not the kinda of programming done on this site. However, you can use a programming language to fill in spreadsheet data. If you were interested in writing data to a document, Python is a very simple language to understand and would be able to do this for you. FCC has tutorials on it, their intro to Python video can be found here.

1 Like
3

Hey, thanks for sharing this problem. You can easily scale my solution with less effort. Please follow these steps:

  1. In a new sheet create this table:
    0.5 Copper Joint 0.20
    0.5 Copper Valve 0.40
    0.5 Copper Tee 0.60
    …all values…

This sheet will be your truth table.

  1. Go back to sheet where you were using if statement.

  2. instead of if try VLOOKUP (Not IF) to search for values.

This solution will easily scale.

2 Likes
4

Thank you, I will give VLOOKUP a shot and let you know how it goes. I feel this is going to simply things quiet abit. I appreciate the answer.

5

Thank you for your response, I knew that excel wasnt a focus on FCC, but i figured with all the programming minds here i might be able to get some help. I am going to try the VLOOKUP as suggested below. But if i can not get that to work then i will rock this out in Python. Thanks for the input.

1 Like
6

Python is an overkill, VLOOKUP is 2 min work…

1 Like
7

You’re welcome! I figured I’d give some opinion for you. I’m glad someone who knew more about excel came along for you. :ok_hand:

1 Like