Formula to work out costings - PLS HELP

Hi guys,

as you can see in the attached spread sheet the sales price is there..

I need to be able to work this out myself.. It needs to total up to the same as the sales price in green.

The buying cost is in the next tab

Can anyone help? :-(

Thank you

cany anyone help?

Originally Posted by m_789

Hi guys,

as you can see in the attached spread sheet the sales price is there..

I need to be able to work this out myself.. It needs to total up to the same as the sales price in green.

The buying cost is in the next tab

Can anyone help? :-(

Thank you

Could you give us some examples the results you're looking for and how you arrive at those results?

an example of the results im looking for is in the green column - Column E..... They would work out the cost by seeing what the cost for the specific charging code is which is in the charging codes tab and then times it by the duration of the call in column C in the CDRS Tab

Originally Posted by BadlySpelledBuoy

Could you give us some examples the results you're looking for and how you arrive at those results?

What would you expect the answer in H2 to be?

0.0024 is what i would expect - it would be the same as e2

Originally Posted by AliGW

What would you expect the answer in H2 to be?

0.0024 is what i would expect - it would be the same as e2

So anything less than 60 seconds will cost the same as one minute - 61 to 120 seconds will cost the same as two minutes, and so on?

seems to have worked for some but not all?
trying to get H2 to total to exactly what e2 totals too :-(
ive attached the sheet to show u

Originally Posted by AliGW

Try this in H2 and copy down: =(HOUR(C2)*60 + MINUTE(C2) + IF(SECOND(C2)>0,1,0))*E2

anything above a minute is not working out right

I don't understand. They are the same. Make sure column H is formatted as general.

How do you want it to work out? Are you saying that up to a minute costs a minute, but anything after that is worked out in fractions? It would have been helpful had you answered my earlier question about this!

it is as general? strange ye? what ive noticed though is everything that the call is over a minute it dont match up the amount in column e & column H

ive attached the image

Originally Posted by AliGW

I don't understand. They are the same. Make sure column H is formatted as general.

i believe its all worked out in fractions?
Im sorry to be a pain i have no idea how its been worked out previously. Tahts what im trying to figure out

Now I am completely confused! C3 = 2 min 34 sec - this rounds up to 3 mins. E3 is 0.0129 (price per minute) and 3 x 0.0129 = 0.0387. What do you expect it to be?

totally lost by it mate im sorry, just want to find the quickest way to do it instead of one by one

Originally Posted by AliGW

How do you want it to work out? Are you saying that up to a minute costs a minute, but anything after that is worked out in fractions? It would have been helpful had you answered my earlier question about this!

So, anything UNDER a minute costs the same as one full minute (60 seconds), but anything OVER a minute is costed out exactly - is this correct?

I have no idea whats going on here mate. Can you paste it in the spreadsheet and upload it here?
I will see it there
god knows where im going wrong

Originally Posted by AliGW

Try this: =(HOUR(C2)*60+MINUTE(C2)+IF((HOUR(C2)+MINUTE(C2)=0),1,SECOND(C2)/60))*E2 and copy down. Format the column as number with four decimal places.

I have already posted the spreadsheet (see my last post). I am a lady, by the way. ;-)

The formula is doing this, basically: if the duration of the call is less than a minute, then the total charge calculated is for a minute. However, any duration over a minute calculates the total cost using minutes AND seconds. I hope that this is what you want, but I'm not entirely sure that YOU know what you want!!! LOL!

but why dont they match up then?

the sales price is different to what we are working it out to be on some of them? why is this?

our work out should be the same as the sales price in green :S

Originally Posted by AliGW

I have already posted the spreadsheet (see my last post). I am a lady, by the way. ;-)

The formula is doing this, basically: if the duration of the call is less than a minute, then the total charge calculated is for a minute. However, any duration over a minute calculates the total cost using minutes AND seconds. I hope that this is what you want, but I'm not entirely sure that YOU know what you want!!! LOL!

I'm sorry, I really don't know what it is you are trying to do. Is the green column the price per minute? If not, then where does it come from and what is it? And if not, where is the price per minute that we should be using in the formula? Are you actually wanting to do calculations based on figures on the other tab in the workbook?

Hi,

Try the following one...

=(HOUR(C2)*60 + MINUTE(C2) + (SECOND(C2)/60))*E2

Thanks

Originally Posted by satabp

=(HOUR(C2)*60 + MINUTE(C2) + (SECOND(C2)/60))*E2

This will not work if the answer in H2 is to be the same as the number in E2, which was discussed further up.

I hope this will work...

=(HOUR(C3)*60 + MINUTE(C3) + IF((HOUR(C3)+MINUTE(C3))>0,(SECOND(C3)/60),1))*E3

Thanks,

okay guys i need to apologise

Fully understood what your saying

This is my fault for not explaining it right!!!!! KILL ME

So basically...

Column G is the relevant charing code for that call.. We will need to find out what the relevant charging code for that call is by matching it up from the "BUYNG COST" TAB and then times that by the duration back in the CDRS tab (CELL C)

Originally Posted by newdoverman

I don't see what your problem is. The Sale Price in column E is a sale price per minute. Column E and Column H will only be the same when the time is less than a minute. The charges as laid out by your costing is that anything over the minute is charged to the next higher minute. If are only charging for 1 minute for the fist minute or part thereof and by the second thereafter then that is different.

This formula, that you already are using in your last worksheet is working correctly for charges by the minute:

Formula:

Please Login or Register  to view this content.

This works equally well producing the same results:

Formula:

Please Login or Register  to view this content.

I have in the attached file, calculated the duration in seconds and the cost per second.