# Solved Excel formula help needed, please!

November 16, 2015 at 15:22:37
Specs: Windows 7
 I am trying to get the below formulas into ONE formula on my spreadsheet.The spreadsheet is in \$ and deals with cost of parts.What I need:If the cost of the part is less than \$90, it needs to be multiplied by 2.1If the cost of the part is equal to or more than \$90, then it needs to be divided by .6If the cost of the part if less than 3.95, the cost is 3.95All costs need to be rounded up to a .95 cents

See More: Excel formula help needed, please!

#1
November 16, 2015 at 16:49:26
 When you say "All costs need to be rounded up to a .95 cents" does that mean this?\$4.94 --> \$4.95\$4.95 --> \$4.95\$4.96 --> \$5.95

Report •

#2
November 16, 2015 at 19:21:50
 Yes, that's right. The cost always rounds UP to .95, too. Like \$4.96 would go up to \$5.95. Make sense?

Report •

#3
November 17, 2015 at 08:24:35
 Try this and see how it works, I have not tested it very extensively, but seems to do what you want:=ROUNDUP(IF(A1="","",IF(A1<=3.95,3.95,IF(A1>=90,A1/0.6,A1*2.1))),0)-0.05MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
November 17, 2015 at 09:32:08
 Beautiful! Thank you so very much! I believe it works in all aspects. :D

Report •

#5
November 17, 2015 at 10:23:31
 Nothing personal towards Mike's solution, but I'm not sure it works in all cases. I say this based on your stated requirements.You said the "The cost always rounds UP to .95" As an example, you also said:"If the cost of the part is equal to or more than \$90, then it needs to be divided by .6"90/.6 = 150.00Mike's formula returns 149.95, which means it rounded down to .95.This formula, which I pieced together from some stuff I found on the web, seems to round everything up to .95 as requested. I'm not even going to try to explain it.=INT(IF(A1<3.95,3.95,IF(A1<90,A1*2.1,A1/0.6)))+0.95+(MOD(IF(A1<3.95,3.95,IF(A1<=90,A1*2.1,A1/0.6)),1)>0.95)This will round the result for 90 up to 150.95, which is what I believe you asked for. If you enter 1 - 100 in Column A and drag both of our formulas down, you will find that whenever the result of the calculation (*2.1 or /.6) is an integer value, Mike's solution "loses" a nickle while mine (well, not really mine) adds 95ยข to the actual result.The correct formula to use will obviously depend on what your actual requirements are.

Report •

#6
November 17, 2015 at 10:27:09
 DerbyDad,You are correct, I checked and it does not ROUNDUP() on the \$90.00 figure,also it gives a #VALUE error if A1 is blank.MIKEmessage edited by mmcconaghy

Report •

#7
November 17, 2015 at 10:38:14
 Apparently my formula will not ROUNDUP() if the value is an even dollar amount.So, \$90 will not round up, while \$90.01 will.This also applies to the dollar amounts of: \$93, \$96, \$99, etc, etc.Like I said, it wasn't extensively tested. MIKEmessage edited by mmcconaghy

Report •

#8
November 17, 2015 at 10:53:27
 Hey, don't worry about. I found the "rounding up to .95" solution via a web search, so it's not like I spent a lot of time on it. :-) All I did was incorporate the "rounding up to .95" portion by substituting the Nested IF in wherever the original formula had a single cell reference.In other words, I got lucky!

Report •

#9
November 17, 2015 at 12:46:10
 I just discovered that myself while poking around with it this morning. Thank you DerbyDad for the update! I appreciate both yours and Mike's time. :)

Report •