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.1
If the cost of the part is equal to or more than $90, then it needs to be divided by .6
If the cost of the part if less than 3.95, the cost is 3.95
All costs need to be rounded up to a .95 cents


See More: Excel formula help needed, please!

Report •


#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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
✔ Best Answer
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.05

MIKE

http://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.00

Mike'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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

MIKE

http://www.skeptic.com/

message 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.

MIKE

http://www.skeptic.com/

message 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!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 •


Ask Question