Forum Discussion
Value Error
Dear Experts,
I have few questions on my calculation below:-
Q1) In "C2", I need to populate the value from Column "Z", corresponding to the value set in N5,
So, if I set N5 = two , it must populate "C2" as 2(numeric)
but Vlookup and filter seems fetching value err.
Q2) In "C3", same way the value corresponding to N4( mapped to column R ) shall be populated,
So, if I put 6 it should be 6 in "C3", but I see if I put "8" in "N4" it populates "7" in my "C3", what wrong I am going?
Attached is the Calculation sheet.
Br,
Anupam
You say it is "breaking this" but don't say how. This should work, maybe try again?
=WRAPROWS( SEQUENCE( ,C3,0),C2, "x")
alternatively you can do a replacement using IF:
=LET( orig, SEQUENCE( ROUNDUP( C3/C2,0), C2, 0, 1), IF( orig<=C3, orig, "x")
6 Replies
Thanks m_tarler that helps :) last question on this issue, so for the SSBs(array) how to Limit the max to the Total No. of SSBs, for example,
in below the :-
Total No. of SSBs =6, then , for nbrOfSsbPerRachOccasion ==4, we should have 2 rows (roundup 6/4), but the max num of SSBs <= Total no of SSB which is 6 here, so in below 0,1,2,3,4,5 are ok and 6 and 7 should not be printed( from C6 to F7 in below case) how to achieve that.
Br,
Anupam
Maybe try this:
=WRAPROWS(SEQUENCE(,6,0),4,"")where the 6 is the toatl# and the 4 is the # of columns
Thanks m_tarler seems this doesn't work, I don't want to break this Sequence formula as it's correct for calculation for the no. of rows and col, for me. but on top of this can we add some formula to omit the SSBs which are >=max no of SSBs, so in below as Total no of SSB ==6( index starts from 0 , so in 2 rows it should be
0 1 2 3
4 5 x x, or we can replace "x" with "", Wraprows is breaking this..
Hi m_tarler , seems attachment is missing.
The problem is how you are using that control. That control will place the INDEX number into that cell from the list so select "two" inserts the number 5 into the cell because that is the 5th in the list and the item "8" is actually the 7th in the list.
So either add INDEX() to your formula or just use Data Validation instead (I added that option to the sheet for you to see)
edit: i will try attaching again. the orig file was .xlsm which this server doesn't like so the attached is an .xlsx
