Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

Excel calculation help

Hi All,

I would like some help with this excel document I have created if possible.  I have left comments in the attached spreadsheet on columns M, N and column AP.  I would like to calculate a percentage of scrap material generated from 2 of the columns, and also calculate how much a price would need to be to hit a certain percentage.

I though this would be best explained in the spreadsheet.  If somebody could please open this and let me know if they can help.  Please ask if further information is required.

Thanks in advance. Example.xls
0
choy77
Asked:
choy77
  • 8
  • 5
  • 3
  • +3
3 Solutions
 
SiddharthRoutCommented:
Spreadsheet missing.

Sid
0
 
barry houdiniCommented:
Hello Choy77, try this formula for M3

=L3-MID(O3,FIND("x",O3)+1,9)

then for N3

=1-M3/L3

format as %

and, I/m not entirely clera for AP, try this in AP3

=AO3*(1+AW3)

that gives you a % increase on AO3 as defined by AW3, is that right? see attached

regards, barry
26880549.xls
0
 
choy77Author Commented:
@SiddharthRout, i didnt upload initially but should be there now.

@barryhoudini, I will give this a go now, thanks for your quick response.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
choy77Author Commented:
Hi barry,

Again thanks for your quick response,

For N3 It needs to show the percentage for M3, can I ask what the minus one is doing at the beginning of the formula in N3? As the figure 6.5 is the scrap from the figure 47 so wouldn't this be much smaller?

For AP3, I would like to show the price which would generate a 33% figure shown in column AW so we have a cost to work towards for the current selling price.  Could you explain the formula in M3 so I can understand as this would be very much appreciated.  Thanks again.
0
 
barry houdiniCommented:
OK, I see

=1-M3/L3 gives you the percentage used, so if you just want the percentage unused (scrap) then that should be just

=M3/L3

For the M3 formula I suggested this

=L3-MID(O3,FIND("x",O3)+1,9)

FIND function finds the "x" in O3 so the mid function effectively gives you everything after the "x", (i,e, the length) and then that's subtracted from the actual length to give the scrap figure

for AP3 I'm still not sure I understand what you require - does the formula I suggested give you the results you expect? If not can you give an example, manually calculated - what should AP3 be given the figures in your example, and how is that calculated?

regards, barry
0
 
choy77Author Commented:
Hi Barry,

Thanks for the explanation to the formulas.  the "Current selling price" in column AO is manually inputted, and then generates a percentage margin against the selling price in column AW.  I would like to some how if possible calculate what the current selling price would need to be (shown in column AP) in order to reach a 33% margin as this is a set target for the sales team.  Basically, if the user types in £20 in column AO and this shows as a 20% in column AW, I would like column AP to automatically show as £33 so the sales person would know that's would price he/she would need to ask for in order to meet the 33%.  I hope this makes sense but please let me know if not.
0
 
choy77Author Commented:
Hi Barry,

Did you manage to have a look at this?

Thanks
0
 
barry houdiniCommented:
Sorry for the delay....but I'm still a little confused. You talk about column AO being manual input but in your attached spreadsheet AO is a calculation, e.g. AO3 shows this formula

=Q3+V3+AF3

AO3 = 18.06 and AW3 is 33.04% so what should AP3 be for those values? I assumed that you want AO3 + 33% so you get that with the formula I suggested, i.e.

=AO3*(1+AW3)

which gives 24.03

Is that the value you would expect?

regards, barry

0
 
choy77Author Commented:
Hi Barry,

Sorry I have given a wrong bit of information there regarding the formula.

AO3 is already over the 33% mark shown in column AW.  I want AP3 to display the exact current selling price that would be required to generate a 33% margin against the selling price in column AW.  

I don't want AP3 to display AO3 + 33%, I just want AP3 to show a single price.  I hope this is abit clearer.

Thanks for your response.
0
 
barry houdiniCommented:
Sorry, perhaps I'm being a little slow on the uptake here but you say

>AO3 is already over the 33% mark shown in column AW

How is that? AO3 is 18.06 so waht are you measuring that against to establish that it's already oevr 33%?

>I want AP3 to display the exact current selling price that would be required to generate a 33% margin against the selling price in column AW

AW doesn't have the selling price - did you mean to refer to another column?

In these situations it's often easier if you can specify the exact result you require, i.e. then actual figures that you want a formula to deliver in AP3:AP6. I'm struggling to understand your written explanation so if I see the actual figures I hope I can ascertain how you came about those (manually) and then I can suggest a formula that would produce those results (or somebody else can)

regards, barry
0
 
choy77Author Commented:
Hi Barry,

Sorry I don't think I have explained it very well.  

The value in column AO is calculated from the Material Price Quoted (Q3) + Quoted Machining Price (V3) + Treatments Price Quoted (AF3) and gives the result of £18.06 in cell AO which generates a percentage margin against selling price of 33.04% in AW3.

I don't know how this would be calculated, but I would like the value in AP3 to somehow display what selling price would be needed to hit a 33% or greater value in column AW.

I suppose something like this although it may look incorrect as I am not good with formulas:

For the formula in AP3 I would like it to do:

=sum(Q3+V3+AP3) / (sum calculation) to generate a 33% or greater result.

I dont know if this will help, I just want a price to be generated here to show what price would be required to hit a 33% figure in column AW?

Could you let me know if this helps?  I have attached a file to try and make it a little clearer.

Thanks.








 Example-2.xls
0
 
choy77Author Commented:
Hi Barry, could you please let me know whether this information is sufficient.
0
 
James0628Commented:
For the 33%, I think the solution is quite simple (although I'll admit that it took me a while to see it).

 AS3*1.5

 If you want the margin % to be 33 % (basically 1/3), the price has to be 50 % more than the cost, so that the difference between them will be 1/3 of the price.  AS3*1.5 should give you a margin of 33.333... %.  If you want the % to be closer to 33.0, you can lower the 1.5 slightly, like to around 1.4925 or 1.4926.

 James
0
 
ElrondCTCommented:
Presumably you want to be able to vary the margin (right now it's 33%, but you might change it to 25%, or 50%), and assuming your definition of "margin" is, "I want x% of the selling price to be profit", the formula you need is:

=A1 / (1 - B1)

where A1 is the price of inputs, and B1 is the margin you want, expressed as x% or 0.xx, the result is your desired selling price.
0
 
James0628Commented:
ElrondCT,

 I assume by "price of inputs" you meant the cost.  "Inputs" could mean different things (like values that are "input" into the spreadsheet), and you'd mentioned "selling price" right before that, so I just assumed that A1 was supposed to be the selling price at first, and couldn't see how that could work.  But if A1 is the cost, then yeah, that's like the calculation that I posted, but more flexible.

 James
0
 
choy77Author Commented:
Thanks very much for your help everybody, it is much appreciated.  I am just going to have a play around with the spreadsheet today I think.

0
 
barry houdiniCommented:
There were 3 questions in all

I attempted to answer all 3 and I believe I answered question 1 to the Asker's satisfaction in my first answer...and then, after clarification, I gave a working answer for question 2 in my second reply, I'm not clear whether the 3rd question was answered at all - the Asker might have to clarify. If no response I suggest equal split between my first 2 posts and also the first posts of both ElrondCT and James0628

regards, barry
0
 
James0628Commented:
FWIW, I basically agree with everything barryhoudini said.  He seems to have answered part of it and I tried to answer another part.  ElrondCT's post was after mine, but has the benefit of being more flexible, which could be useful.

 James
0
 
ElrondCTCommented:
I concur.
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now