This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

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

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

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

@barryhoudini, I will give this a go now, thanks for your quick response.

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.

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.

=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

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.

>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

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

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

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

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

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

=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,

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