Okay, so far I have this for (Sheet 1) A1 =IF('Sheet 2'!B9>=0.5,'Sheet 3'!B3*10,B3*0). There can be a number between 0 & 45 in 'Sheet 3'!B3, But I only want *10 to apply to numbers from 1 to 10 and no higher.
Okay, so far I have this for (Sheet 1) A1 =IF('Sheet 2'!B9>=0.5,'Sheet 3'!B3*10,B3*0). There can be a number between 0 & 45 in 'Sheet 3'!B3, But I only want *10 to apply to numbers from 1 to 10 and no higher.
=IF(Sheet2!B9>=0.5,IF(Sheet3!B3<=10,Sheet3!B3*10,Sheet3!B3),0)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
That is getting me the result of: 0-10 is *10 and anything over 10 is actual B3 value
thats what you asked for
you never mentioned what happens when>10 so i assumed they were left as isBut I only want *10 to apply to numbers from 1 to 10 and no higher.
Yes you are right. I got what I asked for. I should have said I do not want to see any numbers other that 1-10 *10 results. Sorry.
=IF(AND(Sheet2!B9>=0.5,Sheet3!B3<=10),Sheet3!B3*10,0)
or
=IF(AND(Sheet2!B9>=0.5,Sheet3!B3<=10),Sheet3!B3*10,"")
I really appreciate you helping me. Unfortunately I am not conveying the true result set that I need. In a description, this is what I am looking for. When people meet Sheet 2 criteria, I will pay them $10 per point up to 10 points from sheet 3. On sheet 3 is there total points earned, anywhere from 0-45. But I will only pay for the first 10. I am wanting to see how much I need to pay them. If they have 11 - 45 points, I still need to see the $100 I am paying. An example of data is below. Thank you so much.
Pay Points
$10 1
$70 7
$100 10
$100 31
$100 45
=IF(Sheet2!B9>=0.5,MIN(Sheet3!B3*10,100),0)
That is exactly it. Thank you very much for being so patient with me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks