Trying to multiply 2 cells- one contains a long IF & VLOOKUP statement

Cindy Bowker

I have the following problem. I am trying to multiply the value of two cells. One is a numeric value entered into a form the other is the answer to a nested IF ( VLOOKUP) formula. I keep getting and #INVALID VALUE error.

I need the result to be a $ value that comes from the length of rental period selected multiplied by the $ value returned from the following formula -

=IF([Rental Period]1 = "Daily", VLOOKUP([Boom Lifts]1, {Boom Rates}, 2, false), IF([Rental Period]1 = "Weekly", VLOOKUP([Boom Lifts]1, {Boom Rates}, 3, false), IF([Rental Period]1 = "Monthly", VLOOKUP([Boom Lifts]1, {Boom Rates}, 4, false), false)))

The above formula will return a $ value pulled from another sheet based on answers in the form submitted.

Can anyone help?

Thanks,

Cindy

Paul Newcome

Not necessarily. The extra column on the reference sheet was just to test the data. Now that we know what is going on, you can actually delete the extra column on the reference sheet and just modify the VLOOKUP.

VALUE(SUBSTITUTE(VLOOKUP(......................), "$", ""))

Do that with each of the VLOOKUP functions in your IF statement, and you should be good to go.

=IF([Rental Period]1 = "Daily", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 2, false), "$", "")), IF([Rental Period]1 = "Weekly", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 3, false), "$", "")), IF([Rental Period]1 = "Monthly", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 4, false), "$", "")), "")))

Cindy Bowker

It has worked thanks so much for all of your help. I would like to get some more involved training on formulas but that might have to wait until after this Covid-19 crisis.

Have a great day and stay safe.

Cindy

Paul Newcome

Try wrapping the cell reference in your multiplication formula in a VALUE function.

=[First Cell]@row * VALUE([VLOOKUP cell]@row)

Cindy Bowker

=[Length of Rental]1@row*VALUE([Scissor Rental Rate]1@row)

the above is how I have written the formula now. I now have an #UNPARSEABLE error.

not sure where to go from here.

Thanks

Cindy

Paul Newcome

Remove either the row number or @row. You should only use one or the other. The @row reference simply tells the formula to evaluate the column on whatever row the formula is on. It is a little more efficient and helps avoid incorrect row numbers.

Cindy Bowker

=[Length of Rental]@row * VALUE([Scissor Rental Rate]@row)

This is how I have rewritten the formula, I am now getting an #INVALID VALUE error.

Thanks Cindy

Paul Newcome

Is the formula in your original post what is in the [Scissor Rental Rate] column?

Cindy Bowker

=IF([Rental Period]1 = "Daily", VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 2, false), IF([Rental Period]1 = "Weekly", VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 3, false), IF([Rental Period]1 = "Monthly", VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 4, false), "")))

It is this VLOOKUP formula.

Thanks

Cindy

Paul Newcome

Ok. What is in this range?

{Scissor Rates 2}

Is there a formula there or manual entry?

Cindy Bowker

(Scissor Rates 2) comes from my reference sheet, the user manually enters the model of scissor in the form and the rate is populated to the sheet from the formula that is above.

Cindy

Paul Newcome

I understand that you are using the formula above to pull the rate from the source sheet. What I am asking is who the data on the source sheet itself is populated. If the user is entering a model of scissor, where are the numbers coming from on the source sheet that you are pulling?

Cindy Bowker

Sorry,

the user, who is filling in the form, selects the model of scissor from a drop down list. Whatever their response is is then used to find the appropriate pricing. This pricing is from a excel sheet that I imported into a smartsheet to use as a reference.

Paul Newcome

Ok. So it is possible that what you are importing from Excel is being imported as text values instead of numeric.

Let's go to the reference sheet...

Try creating a separate text/number column and entering a formula that will convert your current pricing column into a numeric value.

=VALUE([Pricing Column]@row)

What happens? Are you able to provide a screenshot of the pricing column?

Cindy Bowker

this is the screen shot once I added another column

Paul Newcome

It looks like the data you are pulling from Excel is coming in as text. Let's try this...

=VALUE(SUBSTITUTE([Pricing Column]@row, "$", ""))

Cindy Bowker

okay, so I have entered that formula and I seem to have 3 new columns that give me new pricing values. Do I need to have them in a $ formula.

I am assuming I now need to change my VLOOKup to look for these rows in order to get the proper numeric value so that I can get it to multiply properly.

Thanks

Cindy

Paul Newcome

Not necessarily. The extra column on the reference sheet was just to test the data. Now that we know what is going on, you can actually delete the extra column on the reference sheet and just modify the VLOOKUP.

VALUE(SUBSTITUTE(VLOOKUP(......................), "$", ""))

Do that with each of the VLOOKUP functions in your IF statement, and you should be good to go.

=IF([Rental Period]1 = "Daily", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 2, false), "$", "")), IF([Rental Period]1 = "Weekly", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 3, false), "$", "")), IF([Rental Period]1 = "Monthly", VALUE(SUBSTITUTE(VLOOKUP([Scissor Lifts]1, {Scissor Rates 2}, 4, false), "$", "")), "")))