Got something to say or just want fewer pesky ads? Join us... 😊
[Help] Quick Excel Challenge
- Thread starter matt
- Start date
=if(a1>4, a1*0.9, a1)
Integer: =IF(A1>4, INT(A1*0.9), INT(A1))
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
sorry
Iggle Piggle
Well-known member
- Sep 3, 2010
- 5,960
=if (A1>5,A1*.9,A1)
Make sure it is number no decimal places to get no decimals
Make sure it is number no decimal places to get no decimals
That would be 6 or higher...
Iggle Piggle
Well-known member
- Sep 3, 2010
- 5,960
Moshe Gariani
Well-known member
- Mar 10, 2005
- 12,202
"an 8" "you were right to go higher"
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:
If the number was 5, it would still return 5 as the answer. If it was 5.1, it would multiply by .9, if it was 4.9 it would return 4 (integer) or 5 (rounded)
In which case you can use both INT and ROUND and get the same answers.
B-right-on
Living the dream
Use a calculator? 
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
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
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
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.
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
No worries, it's not even lunchtime yet!
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
