Got something to say or just want fewer pesky ads? Join us... 😊

[Help] Quick Excel Challenge



matt

Well-known member
Mar 19, 2007
1,538
How do I do this:

If the value of cell A1 is 5 or greater, I want B1 to equal 90% of A1. If the value of A1 is 4 or less I want B1 to equal A1. B1 to be an integer.

Cheers
UTA
 






Goldstone1976

We Got Calde in!!
Helpful Moderator
NSC Patreon
Apr 30, 2013
13,766
Herts
Since you only have one cell, can't you just look at it and insert either 90% of its value in B1 or 100% of its value in B1 as appropriate?


sorry
 














sully

Dunscouting
Jul 7, 2003
7,824
Worthing
How do I do this:

If the value of cell A1 is 5 or greater, I want B1 to equal 90% of A1. If the value of A1 is 4 or less I want B1 to equal A1. B1 to be an integer.

Cheers
UTA

Can the values in A1 be fractions?

If so:

Int(if(a1>5,a1*0.9,a1))

If by integer, you just mean a whole number, this will give slightly different results for fractions:

Round(if(a1>5,a1*0.9,a1),0)
 
Last edited:












sully

Dunscouting
Jul 7, 2003
7,824
Worthing
In which case you can use both INT and ROUND and get the same answers.

I take that back!

Having re-read the original post, I see that you want 5 to be multiplied by 0.9, so Bozza was right. However, if you use INT, a 5 in cell A1 will return 4 in B1; if you use ROUND, it will return 5.

So it depends what you actually mean by "B1 to be an integer".

My final (hopefully) say on the formula is therefore:

=INT(IF(A1>4, A1*0.9, A1))
or
=ROUND(IF(A1>4, A1*0.9, A1),0)
depending on what you actually want the results to look like
 




Springal

Well-known member
Feb 12, 2005
23,712
GOSBTS
I know I might be pushing my luck on a Friday... but can someone help me how to work this out

Person wants an average buy price of £430

Currently they buy 1250 of something for £420.43 , and 350 of something for £536

I want to work out what cost of each thing I need to get to, to hit that average
 


KZNSeagull

Well-known member
Nov 26, 2007
19,693
Wolsingham, County Durham
I know I might be pushing my luck on a Friday... but can someone help me how to work this out

Person wants an average buy price of £430

Currently they buy 1250 of something for £420.43 , and 350 of something for £536

I want to work out what cost of each thing I need to get to, to hit that average

Not sure I quite understand what you want, but the way you have phrased it just divide £430 by 1250 to get the average price of 1 of those items. Divide £430 by 350 to get the average price of 1 of the other items. I suspect that this is too simple though.
 


Saunders

Well-known member
Oct 1, 2017
2,292
Brighton
I know I might be pushing my luck on a Friday... but can someone help me how to work this out

Person wants an average buy price of £430

Currently they buy 1250 of something for £420.43 , and 350 of something for £536

I want to work out what cost of each thing I need to get to, to hit that average

You can reduce the average by reducing the cost of 1250 units or the cost of 350 units or reducing both. What do you want to do? The average buy price at the moment for them is £445.71.
 


Shuggie

Well-known member
Sep 19, 2003
666
East Sussex coast
I know I might be pushing my luck on a Friday... but can someone help me how to work this out

Person wants an average buy price of £430

Currently they buy 1250 of something for £420.43 , and 350 of something for £536

I want to work out what cost of each thing I need to get to, to hit that average

No worries, it's not even lunchtime yet!


Not sure I quite understand what you want, but the way you have phrased it just divide £430 by 1250 to get the average price of 1 of those items. Divide £430 by 350 to get the average price of 1 of the other items. I suspect that this is too simple though.

Not sure I do either, but ...

If OP buys 11 of the cheaper ones and 1 of the more expensive ones for every 12 units purchased, the average cost will meet the target of $430 (give or take 6p).

Right, that's enough work today. I'm off to find a Friday lunchtime drinking thread :drink:
 



Paying the bills

Latest Discussions

Paying the bills

Paying the bills

Paying the bills

Albion and Premier League latest from Sky Sports


Top
Link Here