IF function based on the calculations of another cell

hi all, i cant get my functions to work.

S5 is 77 and AD5 is a if/then function. but if i use a function in S5 to get that 77 then AD5 is reading the formula, not the value of 77.

i want AD5 to calculate a pertage based on the answer to S5 so i can use it for markup for pricing. so if S5 is 77 then the if/then would be IF(S5<100,"1.23". it works perfectly as long as i type in 77. but the result i get in AD5 is all over the board depending on where i put the "", whether i leave them out all together, put them around some but not others or all of it, etc. no matter what i do, i cannot get AD5 to answer the correct 1.23 unless i physically type in 77 into S5

Well, "1.23" is text, as opposed to 1.23 which is numeric. No reason why S5<100 should give you anything different to 77<100, unless the formula in S5 is giving an answer other than what you think it is.

Please post a sample workbook.

Here are the amended formulae.

AM2:

Formula:

Please Login or Register  to view this content.

copied down. This corrects for column AM being Text, not Numeric

AD2:

Formula:

Please Login or Register  to view this content.

copied down. As explained, "1.5" is Text, as opposed to just 1.5 which is Numeric.

Column AM, as mentioned, has Text values, not Numeric values. Therefore, your VLOOKUP is returning a Text value, although it "looks like" a Number. Your formula in column AD will also return Text values that look like Numbers.

It's called a double negative. Typically used to coerce Boolean values (True/False) to (1/0). In this case, it is being used as a mathematical operation to convert a text number to a numeric value. It's like multiplying your value by -1 twice.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

You're welcome. Thanks for the rep.