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

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.

Yes, that's right. The cost always rounds UP to .95, too. Like $4.96 would go up to $5.95. Make sense?

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

Beautiful! Thank you so very much! I believe it works in all aspects. :D

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

downto .95.This formula, which I pieced together from some stuff I found on the web, seems to round everything

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

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

message edited by mmcconaghy

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

message edited by mmcconaghy

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.

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. :)

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History