Using VALUE within an IF formula to covert text to number

Tanya Boehmke

This formula

=IF([Projected SI %]@row = 0, "$0", IF([Projected SI %]@row = "MIN", "5000", [Projected Vendor Spend]@row * VALUE(SUBSTITUTE([Projected SI %]@row, "%", "")) / IF([SI Type]@row = "Standard", 100, IF([SI Type]@row = "Fastener", 100, 1))))

Paul Newcome

Would you be able to save a copy of the sheet, remove sensitive data, then share a published version of "Edit by anyone"?

Tanya Boehmke

https://app.smartsheet.com/b/publish?EQBCT=e30b30594b01436e988e70ee80232344

The last row is the one I was testing. The column named [Total Rebate & SI ROI T-3} is the formula I'm trying to fix.

Paul Newcome

I replicated the columns being used and copy/pasted your formulas into place. The result on my sheet is 8,395.0%.

I started really looking at all of the formulas you have in place, and honestly... There is a lot that is overly complicated.

For example... Your formulas that have an output of

"3.5%"

could be rewritten to have an output of

.035

without quotes and then applying the percentage format to the column. That would mean you no longer needed to use the SUBSTITUTE functions in other formulas because now you are generating a numeric value as opposed to a text string.

Those same formulas also output numeric values depending on which IF is found to be true. That means within one column some cells will be a number and some cells will be text even though they both look like numbers.

I am going to try to take a closer look at things and put together some notes for you. It may be a little while before I am able to get to the bottom of it, but I will let you know what I find.

Tanya Boehmke

Paul,

I thought the very same thing! I inherited the excel sheet and never really dug in to the formulas until I had to transfer them to Smartsheet. I appreciate any help you can offer to simplify things!

Paul Newcome

@Tanya Boehmke How comfortable are you with formulas? If I were to give you some pointers, would you be able to follow a pattern to update some?

Tanya Boehmke

I think so....

Paul Newcome

Ok. I will get back to you with some updates an notes later. It may not be until Tuesday though.

Tanya Boehmke

Hi Paul,

Just checking to see if you had a chance to work on this?

Paul Newcome

@Tanya Boehmke My apologies. Work has been crazy. HERE is a published sheet that has some updates to formulas. You will see that a lot of them I just updated row references from specific numbers to @row. There are some where I made minor tweaks such as removing quotes around numbers so that the VALUE function could be removed in other cases (two less parenthesis to misplace).

In the T3 columns, I made updates to those formulas because those were the ones we were originally working with. My suggestion would be to follow those patterns to also update the other Tier columns.

I still have not been able to get the [Total Rebate & SI ROI T-3] to match what you posted earlier, but I have made some progress on getting it a little closer by updating some of the other formulas to flow better.

Feel free to take a look and hopefully using the provided will help as a base to start from to find an actual solution. There are a lot of columns referencing each other within calculations, so being able to really narrow down exactly where the discrepancy is has been rather challenging.

Tanya Boehmke

Thank you Paul, I'll get to work on it!

Paul Newcome

Happy to help. Let me know how it goes, and I will let you know if I make any progress on my end.

The column in question did change when I updated the other formulas, so it could be the data in the other tier columns are affecting it but didn't quite work for me since I manually entered that data.

Tanya Boehmke

Hi Paul,

I just wanted to update you that as of now I have everything simplified and working on my sheet. Hopefully nothing else pops up. Thank you for your help!

Paul Newcome

Excellent! Were those incorrect fields calculating correctly now?

Tanya Boehmke

Yes after adjusting all of the fields, everything is calculating correctly without having to use the Value(Substitute piece.