Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
julmcgrath
 
Posts: n/a
Default gross profit margin formula

I'm trying to figure out the formula for a gross profit margin percentage as
follows:

In Col A2, I will have a cost (i.e. $15.00)
In Col B2, I would like the result to be the total markup of $15.00 based on
a 40% gross profit margin, and then I need the resulting number to be rounded
up or down to the nearest $0.05, ending in a "5."

The resulting number should be $24.95, but I can't get it to come out that
way.
Any suggestions would be appreciated!
  #2   Report Post  
dlw
 
Posts: n/a
Default

Getting the 40% markup is easy, right? The rounding is the tricky pary. Use
this formula:

=A1+0.05-((A1*10) - INT(A1*10))/10

A1 is the cell with the number you want to round.
  #3   Report Post  
David Biddulph
 
Posts: n/a
Default

"dlw" wrote in message
...
Getting the 40% markup is easy, right? The rounding is the tricky pary.

Use
this formula:

=A1+0.05-((A1*10) - INT(A1*10))/10

A1 is the cell with the number you want to round.


But that rounds to 25.05 when the OP was looking for 24.95.

Continuing on dlw's theme, I think you might get the right answer with
=-(-A1+0.05-((-A1*10) - INT(-A1*10))/10)
but I would hope that there might be a more elegant way.
--
David Biddulph


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF & VLOOKUP FORMULA taxmom Excel Worksheet Functions 3 March 2nd 05 04:35 PM
Need Formula or Function to calculate Margin (reverse of Percent a Ken Excel Worksheet Functions 1 February 7th 05 10:26 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 02:27 AM
How do I make a column that calculates gross margin percentage JImv Excel Worksheet Functions 1 December 2nd 04 12:24 AM


All times are GMT +1. The time now is 10:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"