Excel Formula Help Please

B15_Donne

Afternoon,

Can anybody help please.

I need to create a formula where if the volume found is 1500m3 divide by 20 but anything above the 1500m3 is divided by 6.7litres

For instance if the total volume found is 1800m3, the first 1500m3 divide by 20 litres, the remaining 300m3 divide by 6.7litres.

Thanks

Andy_L

=IF(A1>1500,(1500/20)+(A1-1500)/6.7,A1/20)

where A1 is the volume

B15_Donne

Andy_L said:

=IF(A1>1500,(1500/20)+(A1-1500)/6.7,A1/20)

where A1 is the volume

Sorry i’ve made a mistake if the volume is up to 1500m3 multiply by 20litres, if the total volume is over 1800m3, the first 1500m3 is multiplied by 20litres, the remaining 300m3 multiplied by 6.7litres

Andy_L

replace the / with *

=IF(A1>1500,(1500*20)+(A1-1500)*6.7,A1*20)

Heedtheadvice

Did you pass the test?

outtatune

I'm not sure what you mean by 'multiply by x litres' as opposed to 'multiply by x'.

But if that really is what you meant to say then note that 1m3 is not one litre; it's 1000 litres.

Stompa

If we ignore the units, then you could use something like:

=20*MIN(A1,1500)+6.7*MAX(A1-1500,0)

B15_Donne

Thanks for all your help forum. Just started a new job and i’m creating a spreadsheet to make our working lives easier.

Got another question which I need solving, is there a formula when the answer in cell G48 is “Y” then the next cell G49 would multiply B48xC48xB1?

Thank you all

victor2

B15_Donne said:

Thanks for all your help forum. Just started a new job and i’m creating a spreadsheet to make our working lives easier.

Got another question which I need solving, is there a formula when the answer in cell G48 is “Y” then the next cell G49 would multiply B48xC48xB1?

Thank you all

It really sounds like you are not analysing the initial "problem" thoroughly. Look at the starting data and ending result, rather than trying to change results part way through...

Andy_L

B15_Donne said:

Thanks for all your help forum. Just started a new job and i’m creating a spreadsheet to make our working lives easier.

Got another question which I need solving, is there a formula when the answer in cell G48 is “Y” then the next cell G49 would multiply B48xC48xB1?

Thank you all

The IF function

 https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

Heedtheadvice

As Andy but you first need to decide what to do in cell G49 when G48 does not equal "Y".

You could be far better asking, given certain situations how you can achieve certain results. Though what you are doing does help understand formulae that is not always the best way of achieving an objective!